How to create a nested include in Sequelize query

Sequelize allows you to join a third table that’s related to the second table by creating a nested include.

For example, suppose you have three tables with the following relations:

The Users table has one-to-many relation with the Invoices table
The Invoices table has many-to-one relations with the Users table
The Invoices table has many-to-one relations with the Cities table

The tables data would be as shown below:

# Users
+----+-----------+
| id | firstName |
+----+-----------+
|  1 | Nathan    |
|  2 | Jane      |
|  3 | John      |
+----+-----------+

# Invoices
+----+-------+--------+--------+
| id | total | userId | cityId |
+----+-------+--------+--------+
|  1 |   300 |      1 |      5 |
|  2 |   510 |      2 |      1 |
|  3 |   100 |      2 |      3 |
+----+-------+--------+--------+

# Cities
+----+------------+
| id | cityName   |
+----+------------+
|  1 | York       |
|  2 | Bristol    |
|  3 | Manchester |
|  4 | London     |
|  5 | Glasgow    |
+----+------------+

Suppose you are now required to query all three tables data from the Users table.

First, you need to create the associations between the three Sequelize models.

The following should suffice:

const Invoice = sequelize.define(
  "Invoice",
  { total: Sequelize.STRING },
  { timestamps: false }
);

const User = sequelize.define(
  "User",
  { firstName: Sequelize.STRING },
  { timestamps: false }
);

const City = sequelize.define(
  "City",
  { cityName: Sequelize.STRING },
  { timestamps: false }
);

User.hasMany(Invoice);
Invoice.belongsTo(City);

Once the associations are created, you can query the data from the three tables by adding a nested include option into your query method.

The following shows how to add a nested include to the findAll() method:

const data = await User.findAll({
  where: { id: 1 },
  include: [{ model: Invoice, include: [City] }],
});

console.log(JSON.stringify(data, null, 2));

The findAll() method above will generate the following SQL query when connected to MySQL:

SELECT
  `User`.`id`,
  `User`.`firstName`,
  `Invoices`.`id` AS `Invoices.id`,
  `Invoices`.`total` AS `Invoices.total`,
  `Invoices`.`UserId` AS `Invoices.UserId`,
  `Invoices`.`CityId` AS `Invoices.CityId`,
  `Invoices->City`.`id` AS `Invoices.City.id`,
  `Invoices->City`.`cityName` AS `Invoices.City.cityName`
FROM
  `Users` AS `User`
  LEFT OUTER JOIN `Invoices` AS `Invoices` 
    ON `User`.`id` = `Invoices`.`UserId`
  LEFT OUTER JOIN `Cities` AS `Invoices->City` 
    ON `Invoices`.`CityId` = `Invoices->City`.`id`
WHERE
  `User`.`id` = 1;

The query might differ slightly if you’re connected to PostgreSQL.

The data variable will contain the following data from the database:

[
  {
    "id": 1,
    "firstName": "Nathan",
    "Invoices": [
      {
        "id": 1,
        "total": 300,
        "UserId": 1,
        "CityId": 5,
        "City": {
          "id": 5,
          "cityName": "Glasgow"
        }
      }
    ]
  }
]

As you can see, the query result is a nested object that starts with the User object, followed by the Invoices array of objects, and ended with the City object.

By default, a nested include will add another LEFT OUTER JOIN clause to the SQL query.

You can modify the query to use INNER JOIN by adding a where condition inside the include option.

The following JavaScript code:

const data = await User.findAll({
  include: [{ 
    model: Invoice, 
    include: [{
      model: City,
      where: { id: 1 } 
    }] 
  }],
});

console.log(JSON.stringify(data, null, 2));

Will generate the following SQL query:

SELECT
  `User`.`id`,
  `User`.`firstName`,
  `Invoices`.`id` AS `Invoices.id`,
  `Invoices`.`total` AS `Invoices.total`,
  `Invoices`.`UserId` AS `Invoices.UserId`,
  `Invoices`.`CityId` AS `Invoices.CityId`,
  `Invoices->City`.`id` AS `Invoices.City.id`,
  `Invoices->City`.`cityName` AS `Invoices.City.cityName`
FROM
  `Users` AS `User`
  LEFT OUTER JOIN (`Invoices` AS `Invoices`
  INNER JOIN `Cities` AS `Invoices->City` 
    ON `Invoices`.`CityId` = `Invoices->City`.`id`
    AND `Invoices->City`.`id` = 1) ON `User`.`id` = `Invoices`.`UserId`;

By using the INNER JOIN clause, Invoices and City data will only be retrieved when the id of the City matches the condition.

Notice how there’s only one Invoices and City data in the output below:

[
  {
    "id": 1,
    "firstName": "Nathan",
    "Invoices": []
  },
  {
    "id": 2,
    "firstName": "Jane",
    "Invoices": [
      {
        "id": 2,
        "total": 510,
        "UserId": 2,
        "CityId": 1,
        "City": {
          "id": 1,
          "cityName": "York"
        }
      }
    ]
  },
  {
    "id": 3,
    "firstName": "John",
    "Invoices": []
  }
]

And that’s how you can create a nested include query in Sequelize.

When performing a nested include query, you need to make sure that you set the association between the models correctly.

You can learn more about Sequelize association methods here:

How to create JOIN queries with Sequelize

I hope this tutorial has been useful for you 🙏

How to create a nested include in Sequelize query

Take your skills to the next level ⚡️

About

Search

Tags