Skip to content

relationships

Jump to bottom
kapil verma edited this page Jun 30, 2019 · 2 revisions

title: Relationships

Relationships

Definition

tabel ORM provides 8 different types of relations that can be defined between tables. Relationships are defined in table-definitions. For ease of the reader, an example of defining relationships is given below:

orm.defineTable({
  name: 'users',

  props: {
    autoId: true,
    timestamps: true
  },

  relations: {
    posts() {
      return this.hasMany('posts', 'author_id');
    }
  }
});

orm.defineTable({
  name: 'posts',

  props: {
    autoId: true,
    timestamps: true
  },

  relations: {
    author() {
      return this.belongsTo('users', 'author_id');
    }
  }
});

The above example shows how to define hasMany and belongsTo relationships inside table-definitions. We will cover how to define various types of relationships in this section. But before that, we'll cover eager-loading and relation-joins.

Eager-Loading

.eagerLoad(...relations)

Eager-Loading refers to fetching rows from different tables related to the result set being fetched. I hope that wasn't a mouthful. And even if it was, the examples that follow should be enough to clear up what eager-loading is.

Ex 1. Simple Eager-Load: Fetching a list of posts, with each post containing an author key, against which the row from users related via posts.author_id is available as a JS object:

async function getPostsWithAuthorForIds(ids=[]) {
  return await table('posts').eagerLoad('author').whereIn('id', ids).all();
}

Ex 2. Nested Eager-Load: Suppose posts-manyToMany-tags, fetch a list of users, eager-load related posts, and tags related to each post:

async function getUsersWithPostsAndTagsForIds(ids=[]) {
  return await table('users').eagerLoad('posts.tags').whereIn('id', ids).all();
}

Ex 3. Suppose comments have soft-deletes implemented on them, which means that there is a column is_deleted in the comments table. We would have defined a scope on the comments table, called whereNotDeleted(). This scope definition will look something like this:

orm.defineTable({
  name: 'comments',
  ...
  scopes: {
    ...
    whereNotDeleted() {
      return this.where('is_deleted', false);
    }
  }
  ...
});

Now, suppose we want to fetch a list of posts, along with related, not-deleted comments. In this scenario, you have two options.

Option 1: Constrained Eager-Load: Constrain eager-loads at the time of fetching data:

async function getPostsWithCommentsByIds(ids=[]) {
  return await table('posts')
    .eagerLoad({comments: (t) => t.whereNotDeleted()})
    // the "t" passed to the closure is an object that represents `comments` table
    .where('id', 'in', ids)
    .all()
  ;
}

Option 2: Constrained Relation Definition: Constrain the relationship at the time of its definition:

orm.defineTable({
  name: 'posts',
  ...
  relations: {
    ...
    comments() {
      return this.hasMany('comments', 'post_id').constrain((t) => t.whereNotDeleted());
      // the "t" passed to the closure is an object that represents `comments` table 
    }
  }
});

...

// Now you can eager-load comments when fetching posts freely

async function getPostsWithCommentsByIds(ids=[]) {
  return await table('posts').eagerLoad('comments').where('id', 'in', ids).all();
}

That's all on eager-loading. All the approaches shown work well with each other. You can mix-and-match them as you see fit.

Relation-Joins

When you define relationships in a table-definition, they give you the ability to join related(or intermediary, in the case of manyToMany, and hasManyThrough) tables to the main table, at the time of performing a query on the main table. This feature may sound complex, but is, in fact very easy to use.

Consider the following table-definitions:

orm.defineTable({
  name: 'posts',

  props: {
    autoId: true,
    timestamps: true
  },

  relations: {
    comments() {
      return this.hasMany('comments', 'post_id');
    },

    tags() {
      return this.manyToMany('tags', 'post_tag', 'post_id', 'tag_id');
    }
  }
});

orm.defineTable({
  name: 'comments',

  props: {
    autoId: true,
    timestamps: true
  },

  relations: {
    post() {
      return this.belongsTo('posts', 'post_id');
    }
  }
});

orm.defineTable({
  name: 'tags',

  props: {
    autoId: true,
    timestamps: true
  },

  relations: {
    posts() {
      return this.manyToMany('posts', 'post_tag', 'tag_id', 'post_id');
    }
  }
});

orm.defineTable({
  name: 'post_tag',

  props: {
    key: ['post_id', 'tag_id'],
    autoId: false,
    timestamps: true
  }
});

The relationships in the above shown table-definitions can be summarised as follows:

  1. posts - hasMany - comments
  2. posts - manyToMany - tags - via pivot - post_tag
  3. comments - belongsTo - posts
  4. tags - manyToMany - posts - via pivot - post_tag

Now that we have defined these relationships, we can perform join operations across these tables very, very easily. These join operations are implemented using joints, hence, you can use the join operations freely, without bothering about attempting to double-join a table. Examples below:

Example 1: Fetch posts which have comments by a certain user:

async function getPostsWithCommentsByUser(user) {
  return await table('posts').comments().join().where('comments.user_id', user.id).all();
}

Example 2: Fetch posts which do not have any comments on them:

async function getPostsWithoutComments() {
  return await table('posts').comments().leftJoin().whereNull('comments.id').all();
}

Example 3: Fetch posts which have certain tags attached to them:

async function getPostsWithTags(tags=[]) {
  return await table('posts').tags().joinPivot().whereIn('post_tag.id', tags.map(({id}) => id)).distinct().all();
}

Example 4: Fetch tags which are not attached to any posts:

async function getTagsWithoutPosts() {
  return await table('tags').posts().leftJoin().whereNull('posts.id').all();
}

Hope these examples are enough to demonstrate the concept behind relation-joins, and to showcase the power and brevity they bring to tabel ORM. We will catalogue different types of relationships next, and list the relation-joins available on those relationships in their respective sections.

hasOne

hasOne(related, foreignKey, key)

Arguments:

  1. related: Related table's name.
  2. foreignKey: Related table's column that refers to key on current table.
  3. key: Current table's column which is referenced by foreignKey column of related table. Defaults to current table's key prop.

Definition:

orm.defineTable({
  name: 'posts',
  ...
  relations: {
    code() {
      return this.hasOne('codes', 'post_id');
    }
  }
});

Inner-Join:

async function getPostByCode(code) {
  return await table('posts').code().join().where('codes.value', code).first();
}

Left-Join:

async function getPostsWithoutCode(code) {
  return await table('posts').code().leftJoin().whereNull('codes.id').all();
}

hasMany

hasMany(related, foreignKey, key)

Arguments:

  1. related: Related table's name.
  2. foreignKey: Related table's column that refers to key on current table.
  3. key: Current table's column which is referenced by foreignKey column of related table. Defaults to current table's key prop.

Definition:

orm.defineTable({
  name: 'posts',
  ...
  relations: {
    comments() {
      return this.hasMany('comments', 'post_id');
    }
  }
});

Inner-Join:

async function getPostsWithCommentsByUser(user) {
  return await table('posts').comments().join().where('comments.user_id', user.id).all();
}

Left-Join:

async function getPostsWithoutComments() {
  return await table('posts').comments().leftJoin().whereNull('comments.id').all();
}

hasManyThrough

hasManyThrough(related, through, firstKey, secondKey)

Arguments:

  1. related: Related table's name.
  2. through: Through table's name.
  3. firstKey: Column on through table that refers to current table's key prop.
  4. secondKey: Column on related table that refers to through table's key prop.

Definition:

orm.defineTable({
  name: 'users',
  ...
  relations: {
    ...
    receivedComments() {
      return this.hasManyThrough('comments', 'posts', 'author_id', 'post_id');
    }
  }
});

orm.defineTable({
  name: 'posts',
  ...
  relations: {
    ...
  }
});

orm.defineTable({
  name: 'comments',
  ...
  relations: {
    ...
  }
});

Fetching Extra Through Columns: Consider the scenario in the previous code-sample. Suppose you have some code like this:

async function getUsersWithReceivedComments() {
  return await table('users').eagerLoad('receivedComments').all();
}
...
const users = await getUsersWithReceivedComments();

The const users will be an array of users, each containing a key receivedComments, which will be an array of rows from comments table. Each comment object will contain an extra field named through, which by default will be an object with two keys, the primary-key of posts table, the through-table in this scenario, and the column firstKey. If you want to fetch more data from the through table, for example, the title of a post, you'll need to add that detail to the relationship's definition. Shown below:

orm.defineTable({
  name: 'users',
  ...
  relations: {
    ...
    receivedComments() {
      return this.hasManyThrough('comments', 'posts', 'author_id', 'post_id')
        .withThrough('title', 'created_at')
      ;
    }
  }
});

Inner-Join Related:

async function getUsersWhichReceivedGivenComments(comments=[]) {
  return await table('users').receivedComments().join().whereIn('comments.id', comments.map(({id}) => id)).all();
}

Inner-Join Through: Assume the following table-definition:

orm.defineTable({
  name: 'users',
  ...
  relations: {
    posts() {
      return this.hasMany('posts', 'author_id');
    },

    receivedComments() {
      return this.hasManyThrough('comments', 'posts', 'author_id', 'post_id');
    }
  }
});

Then the following two lines of code are equivalent:

table('users').receivedComments().joinThrough();
table('users').posts().join();

Left-Join Related:

table('users').receivedComments().leftJoin().where(...);

Left-Join Through:

table('users').receivedComments().leftJoinThrough().where(...);

belongsTo

belongsTo(related, foreignKey, otherKey)

Arguments:

  1. related: Related table's name.
  2. foreignKey: Foreign-key column in the current table.
  3. otherKey: Referenced-key column in the related table. Defaults to related table's key prop.

Definition:

orm.defineTable({
  name: 'posts',
  ...
  relations: {
    author() {
      return this.belongsTo('users', 'author_id');
    }
  }
});

Inner-Join:

table('posts').author().join().where('users.email', '=', ...);

Left-Join:

table('posts').author().leftJoin().whereNull('users.id');

manyToMany

manyToMany(related, pivot, foreignKey, otherKey, joiner=((j) => {}))

Arguments:

  1. related: Related table's name.
  2. pivot: Pivot table's name.
  3. foreignKey: Foreign-key column on the pivot table that points to the current table's key prop.
  4. otherKey: Foreign-key column on the pivot table that points to the related table's key prop.
  5. joiner: A closure which accepts the join of related and pivot tables. Can apply further conditions on the join.

Definition:

orm.defineTable({
  name: 'posts',
  ...
  relations: {
    tags() {
      return this.manyToMany('tags', 'post_tag', 'post_id', 'tag_id');
    }
  }
});

orm.defineTable({
  name: 'tags',
  ...
  relations: {
    return this.manyToMany('posts', 'post_tag', 'tag_id', 'post_id');
  }
});

orm.defineTable({
  name: 'post_tag',

  props: {
    autoId: false,
    key: ['post_id', 'tag_id'],
    ...
  }
});

The above example contains definitions of both posts-manyToMany-tags and tags-manyToMany-posts. Defining both the relations is not necessary, but is generally a good idea.

Fetching Extra Pivot Columns: Consider the scenario in the previous code-sample. Suppose you have some code like this:

async function getPostsWithTags() {
  return await table('posts').eagerLoad('tags').all();
}
...
const posts = await getPostsWithTags();

The const posts will be an array of posts, each containing a key tags, which will be an array of rows from tags table. Each tag object will contain an extra field named pivot, which by default will be an object with two keys, the foreign-key referencing posts table, and the foreign-referencing the tags table. If you want to fetch more data from the pivot table, for example, weight associated with each tag for each post, you'll need to add that detail to the relationship's definition. Shown below:

orm.defineTable({
  name: 'posts',

  relations: {
    tags() {
      return this.manyToMany('tags', 'post_tag', 'post_id', 'tag_id')
        .withPivot('weight', 'created_at')
      ;
    }
  }
});

Inner-Join Related:

table('posts').tags().join().where('tags.name', ...);

Inner-Join Pivot:

table('posts').tags().joinPivot().whereIn('post_tag.tag_id', ...);

Left-Join Related:

table('posts').tags().leftJoin().whereNull('tags.id');

Left-Join Pivot:

table('posts').tags().leftJoinPivot().whereNull('post_tag.tag_id');

morphOne

morphOne(related, inverse)

This type of relationship allows multiple tables to connect to a single table, and for rows in that single table to refer to entities in those multiple tables. This particular type of relationship, ie, morphOne is defined in one of those multiple tables.

Arguments:

  1. related: Related table's name. This table's table-definition must contain a morphTo relation for morphOne relation to function properly.
  2. inverse: Inverse relation defined on the related table's table-definition.

Definition:

orm.defineTable({
  name: 'posts',
  ...
  relations: {
    metaItem() {
      return this.morphOne('meta_items', 'record');
    }
  }
});

orm.defineTable({
  name: 'tags',
  ...
  relations: {
    metaItem() {
      return this.morphOne('meta_items', 'record');
    }
  }
});

Inner-Join:

table('posts').item().join();

Left-Join:

table('posts').item().leftJoin();

morphMany

morphMany(related, inverse)

This type of relationship allows multiple tables to connect to a single table, and for rows in that single table to refer to entities in those multiple tables. This particular relationship, ie, morphMany is defined in one of those multiple tables.

Arguments:

  1. related: Related table's name. This table's table-definition must contain a morphTo relation for morphOne relation to function properly.
  2. inverse: Inverse relation defined on the related table's table-definition.

Definition:

orm.defineTable({
  name: 'posts',
  ...
  relations: {
    metaCategories() {
      return this.morphMany('meta_categories', 'record');
    }
  }
});

orm.defineTable({
  name: 'tags',
  ...
  relations: {
    metaItem() {
      return this.morphMany('meta_categories', 'record');
    }
  }
});

Inner-Join:

table('posts').item().join();

Left-Join:

table('posts').item().leftJoin();

morphTo

morphTo(tables, typeField, foreignKey)

This type of relationship allows multiple tables to connect to a single table, and for rows in that single table to refer to entities in those multiple tables. This particular type of relationship, ie, morphTo, is defined in the single table. Is eager-loadable.

Arguments:

  1. tables: Array of table-names that will be referenced by rows of the current table.
  2. typeField: Column which stores the name of the related table in the current table.
  3. foreignKey: Column which references the key prop column of the table stored in typeField column.

Definition:

orm.defineTable({
  name: 'items',
  ...
  relations: {
    record() {
      return this.morphTo(['posts', 'tags'], 'item_type', 'item_id');
    }
  }
});

Inner-Join:

table('items').record().join('posts');

Left-Join:

table('items').record().leftJoin('posts');

Next > Fetching Data

Clone this wiki locally