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. })
    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.   */
    20. User.findAll({ include: [{ association: 'Instruments' }] }).then(users => {
    21.   console.log(JSON.stringify(users))
    24.     [{
    25.       "name": "John Doe",
    26.       "id": 1,
    27.       "createdAt": "2013-03-20T20:31:45.000Z",
    28.       "updatedAt": "2013-03-20T20:31:45.000Z",
    29.       "Instruments": [{
    30.         "name": "Toothpick",
    31.         "id": 1,
    32.         "createdAt": null,
    33.         "updatedAt": null,
    34.         "userId": 1
    35.       }]
    36.     }]
    37.   */
    38. })

    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.

    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.

    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 }]});

    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

    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' ] ]

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

    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

    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.     }]