Eager loading

When you are retrieving data from the database there is a fair chance that you also want to get associations with the same query - this is called eager loading. The basic idea behind that, is the use of the attribute include when you are calling find or findAll. Lets assume the following setup:

  1. class User extends Model {}
  2. User.init({ name: Sequelize.STRING }, { sequelize, modelName: 'user' })
  3. class Task extends Model {}
  4. Task.init({ name: Sequelize.STRING }, { sequelize, modelName: 'task' })
  5. class Tool extends Model {}
  6. Tool.init({ name: Sequelize.STRING }, { sequelize, modelName: 'tool' })
  8. Task.belongsTo(User)
  9. User.hasMany(Task)
  10. User.hasMany(Tool, { as: 'Instruments' })
  12. sequelize.sync().then(() => {
  13.   // this is where we continue ...
  14. })

OK. So, first of all, let's load all tasks with their associated user.

  1. Task.findAll({ include: [ User ] }).then(tasks => {
  2.   console.log(JSON.stringify(tasks))
  4.   /*
  5.     [{
  6.       "name": "A Task",
  7.       "id": 1,
  8.       "createdAt": "2013-03-20T20:31:40.000Z",
  9.       "updatedAt": "2013-03-20T20:31:40.000Z",
  10.       "userId": 1,
  11.       "user": {
  12.         "name": "John Doe",
  13.         "id": 1,
  14.         "createdAt": "2013-03-20T20:31:45.000Z",
  15.         "updatedAt": "2013-03-20T20:31:45.000Z"
  16.       }
  17.     }]
  18.   */
  19. })

Notice that the accessor (the User property in the resulting instance) is singular because the association is one-to-something.

Next thing: Loading of data with many-to-something associations!

  1. User.findAll({ include: [ Task ] }).then(users => {
  2.   console.log(JSON.stringify(users))
  4.   /*
  5.     [{
  6.       "name": "John Doe",
  7.       "id": 1,
  8.       "createdAt": "2013-03-20T20:31:45.000Z",
  9.       "updatedAt": "2013-03-20T20:31:45.000Z",
  10.       "tasks": [{
  11.         "name": "A Task",
  12.         "id": 1,
  13.         "createdAt": "2013-03-20T20:31:40.000Z",
  14.         "updatedAt": "2013-03-20T20:31:40.000Z",
  15.         "userId": 1
  16.       }]
  17.     }]
  18.   */
  19. })

Notice that the accessor (the Tasks property in the resulting instance) is plural because the association is many-to-something.

If an association is aliased (using the as option), you must specify this alias when including the model. Notice how the user's Tools are aliased as Instruments above. In order to get that right you have to specify the model you want to load, as well as the alias:

  1. User.findAll({ include: [{ model: Tool, as: 'Instruments' }] }).then(users => {
  2.   console.log(JSON.stringify(users))
  4.   /*
  5.     [{
  6.       "name": "John Doe",
  7.       "id": 1,
  8.       "createdAt": "2013-03-20T20:31:45.000Z",
  9.       "updatedAt": "2013-03-20T20:31:45.000Z",
  10.       "Instruments": [{
  11.         "name": "Toothpick",
  12.         "id": 1,
  13.         "createdAt": null,
  14.         "updatedAt": null,
  15.         "userId": 1
  16.       }]
  17.     }]
  18.   */
  19. })

You can also include by alias name by specifying a string that matches the association alias:

  1. User.findAll({ include: ['Instruments'] }).then(users => {
  2.   console.log(JSON.stringify(users))
  4.   /*
  5.     [{
  6.       "name": "John Doe",
  7.       "id": 1,
  8.       "createdAt": "2013-03-20T20:31:45.000Z",
  9.       "updatedAt": "2013-03-20T20:31:45.000Z",
  10.       "Instruments": [{
  11.         "name": "Toothpick",
  12.         "id": 1,
  13.         "createdAt": null,
  14.         "updatedAt": null,
  15.         "userId": 1
  16.       }]
  17.     }]
  18.   */
  19. })
  21. User.findAll({ include: [{ association: 'Instruments' }] }).then(users => {
  22.   console.log(JSON.stringify(users))
  24.   /*
  25.     [{
  26.       "name": "John Doe",
  27.       "id": 1,
  28.       "createdAt": "2013-03-20T20:31:45.000Z",
  29.       "updatedAt": "2013-03-20T20:31:45.000Z",
  30.       "Instruments": [{
  31.         "name": "Toothpick",
  32.         "id": 1,
  33.         "createdAt": null,
  34.         "updatedAt": null,
  35.         "userId": 1
  36.       }]
  37.     }]
  38.   */
  39. })

When eager loading we can also filter the associated model using where. This will return all Users in which the where clause of Tool model matches rows.

  1. User.findAll({
  2.     include: [{
  3.         model: Tool,
  4.         as: 'Instruments',
  5.         where: { name: { [Op.like]: '%ooth%' } }
  6.     }]
  7. }).then(users => {
  8.     console.log(JSON.stringify(users))
  10.     /*
  11.       [{
  12.         "name": "John Doe",
  13.         "id": 1,
  14.         "createdAt": "2013-03-20T20:31:45.000Z",
  15.         "updatedAt": "2013-03-20T20:31:45.000Z",
  16.         "Instruments": [{
  17.           "name": "Toothpick",
  18.           "id": 1,
  19.           "createdAt": null,
  20.           "updatedAt": null,
  21.           "userId": 1
  22.         }]
  23.       }],
  25.       [{
  26.         "name": "John Smith",
  27.         "id": 2,
  28.         "createdAt": "2013-03-20T20:31:45.000Z",
  29.         "updatedAt": "2013-03-20T20:31:45.000Z",
  30.         "Instruments": [{
  31.           "name": "Toothpick",
  32.           "id": 1,
  33.           "createdAt": null,
  34.           "updatedAt": null,
  35.           "userId": 1
  36.         }]
  37.       }],
  38.     */
  39.   })

When an eager loaded model is filtered using include.where then include.required is implicitly set totrue. This means that an inner join is done returning parent models with any matching children.

Top level where with eagerly loaded models

To move the where conditions from an included model from the ON condition to the top level WHERE you can use the '$nested.column$' syntax:

  1. User.findAll({
  2.     where: {
  3.         '$Instruments.name$': { [Op.iLike]: '%ooth%' }
  4.     },
  5.     include: [{
  6.         model: Tool,
  7.         as: 'Instruments'
  8.     }]
  9. }).then(users => {
  10.     console.log(JSON.stringify(users));
  12.     /*
  13.       [{
  14.         "name": "John Doe",
  15.         "id": 1,
  16.         "createdAt": "2013-03-20T20:31:45.000Z",
  17.         "updatedAt": "2013-03-20T20:31:45.000Z",
  18.         "Instruments": [{
  19.           "name": "Toothpick",
  20.           "id": 1,
  21.           "createdAt": null,
  22.           "updatedAt": null,
  23.           "userId": 1
  24.         }]
  25.       }],
  27.       [{
  28.         "name": "John Smith",
  29.         "id": 2,
  30.         "createdAt": "2013-03-20T20:31:45.000Z",
  31.         "updatedAt": "2013-03-20T20:31:45.000Z",
  32.         "Instruments": [{
  33.           "name": "Toothpick",
  34.           "id": 1,
  35.           "createdAt": null,
  36.           "updatedAt": null,
  37.           "userId": 1
  38.         }]
  39.       }],
  40.     */

Including everything

To include all attributes, you can pass a single object with all: true:

  1. User.findAll({ include: [{ all: true }]});

Including soft deleted records

In case you want to eager load soft deleted records you can do that by setting include.paranoid to false

  1. User.findAll({
  2.     include: [{
  3.         model: Tool,
  4.         where: { name: { [Op.like]: '%ooth%' } },
  5.         paranoid: false // query and loads the soft deleted records
  6.     }]
  7. });

Ordering Eager Loaded Associations

In the case of a one-to-many relationship.

  1. Company.findAll({ include: [ Division ], order: [ [ Division, 'name' ] ] });
  2. Company.findAll({ include: [ Division ], order: [ [ Division, 'name', 'DESC' ] ] });
  3. Company.findAll({
  4.   include: [ { model: Division, as: 'Div' } ],
  5.   order: [ [ { model: Division, as: 'Div' }, 'name' ] ]
  6. });
  7. Company.findAll({
  8.   include: [ { model: Division, as: 'Div' } ],
  9.   order: [ [ { model: Division, as: 'Div' }, 'name', 'DESC' ] ]
  10. });
  11. Company.findAll({
  12.   include: [ { model: Division, include: [ Department ] } ],
  13.   order: [ [ Division, Department, 'name' ] ]
  14. });

In the case of many-to-many joins, you are also able to sort by attributes in the through table.

  1. Company.findAll({
  2.   include: [ { model: Division, include: [ Department ] } ],
  3.   order: [ [ Division, DepartmentDivision, 'name' ] ]
  4. });

Nested eager loading

You can use nested eager loading to load all related models of a related model:

  1. User.findAll({
  2.   include: [
  3.     {model: Tool, as: 'Instruments', include: [
  4.       {model: Teacher, include: [ /* etc */]}
  5.     ]}
  6.   ]
  7. }).then(users => {
  8.   console.log(JSON.stringify(users))
  10.   /*
  11.     [{
  12.       "name": "John Doe",
  13.       "id": 1,
  14.       "createdAt": "2013-03-20T20:31:45.000Z",
  15.       "updatedAt": "2013-03-20T20:31:45.000Z",
  16.       "Instruments": [{ // 1:M and N:M association
  17.         "name": "Toothpick",
  18.         "id": 1,
  19.         "createdAt": null,
  20.         "updatedAt": null,
  21.         "userId": 1,
  22.         "Teacher": { // 1:1 association
  23.           "name": "Jimi Hendrix"
  24.         }
  25.       }]
  26.     }]
  27.   */
  28. })

This will produce an outer join. However, a where clause on a related model will create an inner join and return only the instances that have matching sub-models. To return all parent instances, you should add required: false.

  1. User.findAll({
  2.   include: [{
  3.     model: Tool,
  4.     as: 'Instruments',
  5.     include: [{
  6.       model: Teacher,
  7.       where: {
  8.         school: "Woodstock Music School"
  9.       },
  10.       required: false
  11.     }]
  12.   }]
  13. }).then(users => {
  14.   /* ... */
  15. })

The query above will return all users, and all their instruments, but only those teachers associated with Woodstock Music School.

Include all also supports nested loading:

  1. User.findAll({ include: [{ all: true, nested: true }]});

Use right join for association

By default, associations are loaded using a left join, that is to say it only includes records from the parent table. You can change this behavior to a right join by passing the right property, if the dialect you are using supports it. Currenly, sqlite does not support right joins.

Note: right is only respected if required is false.

  1. User.findAll({
  2.     include: [{
  3.         model: Tool // will create a left join
  4.     }]
  5. });
  7. User.findAll({
  8.     include: [{
  9.         model: Tool,
  10.         right: true // will create a right join
  11.     }]
  12. });
  14. User.findAll({
  15.     include: [{
  16.         model: Tool,
  17.         required: true,
  18.         right: true // has no effect, will create an inner join
  19.     }]
  20. });
  22. User.findAll({
  23.     include: [{
  24.         model: Tool,
  25.         where: { name: { [Op.like]: '%ooth%' } },
  26.         right: true // has no effect, will create an inner join
  27.     }]
  28. });
  30. User.findAll({
  31.     include: [{
  32.         model: Tool,
  33.         where: { name: { [Op.like]: '%ooth%' } },
  34.         required: false
  35.         right: true // because we set `required` to false, this will create a right join
  36.     }]
  37. });