querying

Joins

Joining tables is done using the join option. Not to be confused with the include option.

Query Builder

const users = await User
  .select('user')
  .join(Post, qb => {
    qb.on('userId', col('user.id'))
  })
  .findAll();

POJO

const users = await User.findAll({
  alias: 'user',
  join: [{ 
    target: Post,
    on: {
      userId: col('user.id'),
    },
  }],
});

Joins use LEFT JOIN by default, but can be set to another type using the type option.

const users = await User.findAll({
  alias: 'user',
  join: [{
    target: Post,
    type: 'inner',
    on: {
      userId: col('user.id'),
    },
  }],
});

Specifying the joined table

The model option can be a model class or an association. However, there are some differences between the two.

Using an Association

Specifying an association is the easiest way to join a table. It will automatically use the correct table name and the correct ON clause. Of course, you can always provide your own on clause if you want to. Your clause will replace the one generated by default.

const users = await User.findAll({
  alias: 'user',
  join: [{
    // no need to specify an ON clause, it will be automatically 
    // built from the association.
    target: User.getAssociations().posts,
  }],
});

Using a Model class

Using a model class grants a little more flexibility. The two models don't need to be associated. You must however provide the ON clause yourself.

const users = await User.findAll({
  alias: 'user',
  join: [{ 
    target: Post,
    on: {
      userId: {
        [Op.eq]: col('user.id'),
      },
    },
  }],
});

tip

You can alias that target model using the alias option. This alias can be used to reference the table's attributes:

const users = await User.findAll({
  alias: 'user',
  join: [{
    target: Post,
    alias: 'post',
    on: {
      userId: col('user.id'),
    },
  }],
  where: {
    ['post.title']: 'foo',
  },
});

Differences between include and join

join and include look very similar, but they are not the same. You can think of join as the low-level API, and include as a high-level API - built on top of join - that simplifies some common use-cases.

Complex queries will often prefer to use join directly, while relatively straightforward ones will prefer include.

Advantages of join

• You have complete control over the type of join
• You can customize the join condition
• The where option can reference attributes from all joins. With include, a where condition can only reference attributes from itself or its parent model.
• Joins can reference any table. include only works with tables that are associated with the main model using Sequelize's association system.

Advantages of include

• limit is applied per include, whereas when using a join the limit will be applied to the cartesian product of all joined tables.
• Supports splitting the query in multiple queries through the separate option.
• Many-to-Many includes don't require specifying the through table join.
  When using a join, Many-To-Many associations require 2 joins: one for the through table and one for the target table.

Subqueries

Subqueries can go (almost) anywhere by using raw queries, in FROM, JOIN, in the list of attributes, etc…

Multiple options are available for writing subqueries:

Using the subquery API

Query Builder

import { subquery, Op, col } from '@sequelize/core';

await User
  .select()
  .where('id', Op.in, subquery(qb => {
    qb.target(User, 'u')
      .attributes(['id'])
      .distinct()
      .innerJoin(Project, 'p', qb => {
        qb.on({ 
          userId: col('u.id'),
        })
      })
      .where({
        'p.name': 'foo',
      });
  }))
  .findAll();

POJO

import { subquery, Op, col } from '@sequelize/core';

await User.findAll({
  where: {
    id: {
      [Op.in]: subquery({
        target: User,
        alias: 'u',
        distinct: true,
        attributes: ['id'],
        join: {
          target: Project,
          alias: 'u',
          type: 'inner',
          on: {
            userId: col('u.id'),
          },
        },
        where: {
          'p.name': 'foo',
        },
      }),
    }
  },
});

The above (roughly) produces the following SQL:

SELECT "user".*
FROM "users" AS "user"
WHERE "user"."id" IN (
  SELECT DISTINCT "u"."id"
  FROM "users" AS "u"
  INNER JOIN "projects" AS "p"
    ON "p"."userId" = "u"."id"
  WHERE "p"."name" = 'foo'
);

Using raw SQL

Query Builder

import { table, sql, Op } from '@sequelize/core';

await User
  .select()
  .where('id', Op.in, sql`(
    SELECT DISTINCT u.id FROM ${table(User)} u 
    INNER JOIN ${table(Project)} p 
      ON u.id = p.user_id 
    WHERE p.name = 'foo'
  )`)
  .findAll();

POJO

import { table, sql, Op } from '@sequelize/core';

await User.findAll({
  where: {
    id: {
      [Op.in]: sql`(
        SELECT DISTINCT u.id FROM ${table(User)} u 
        INNER JOIN ${table(Project)} p 
          ON u.id = p.user_id 
        WHERE p.name = 'foo'
      )`,
    },
  },
});

Subqueries in the FROM clause

The from option can be used to specify a subquery to use in the FROM clause, instead of a model's table.

Sequelize expects that subquery to return the same columns as the table it is replacing.

import { sql, table } from '@sequelize/core';

await User.findAll({
  // Note how you have to provide your own parenthesis when using raw SQL, to give you more control over the query.
  from: sql`(SELECT * FROM ${table(User)} LIMIT 10)`,
});

The above (roughly) produces the following SQL:

SELECT "users".*
FROM (
  SELECT * FROM users LIMIT 10
) AS "users";

Subqueries in JOIN clauses

The above from clause can also be used to specify a subquery to use in a JOIN clause.

import { sql } from '@sequelize/core';

await User.findAll({
  alias: 'u',
  joins: {
    target: Project,
    // This fetches 10 projects per user.
    from: sql`(SELECT * FROM ${table(Project)} p WHERE p.user_id = u.id LIMIT 10)`,
    type: sql`LEFT JOIN LATERAL`,
    on: true,
  },
});

The above (roughly) produces the following SQL:

SELECT "u".*
FROM "users" AS "u"
LEFT JOIN LATERAL (
  SELECT * FROM projects p WHERE p.user_id = u.id LIMIT 10
) AS "projects" ON true;

Includes

Includes are designed to cover the most common use cases related to fetching associations. They are built on top of SQL Joins and Subqueries, but are not a replacement for them. There are use-cases that are not covered by the include option, and you will need to use joins or subqueries instead or in addition to include.

Below are some common use-cases that are easily covered using include.

Having a limit per include

The "limit" parameter only applies to its level. In the following example, the query will select a maximum of 3 users, and will select up to 2 projects for each of those users (for a maximum of 6 total projects):

await User.findAll({
  // only 3 users will be returned
  limit: 3,
  include: {
    association: User.associations.projects,
    // 2 projects per user will be returned
    limit: 2,
  },
});

If you do not set a limit on an association, all associated entities will be included.

caution

It is possible to set a limit on all include levels, but be aware that the deeper a limited associated is, the more complex the query becomes.

The query can change drastically depending on the dialect you use.
We recommend using the logging option to see what your query looks like. If your query becomes too complex, consider using the separate option to automatically fetch that associated in a subsequent query.

Filtering associations

Each include can have its own where clause, which will be used to filter the associated entities.

await User.findAll({
  include: {
    association: User.associations.projects,
    where: {
      // Only projects named "foo" will be included
      name: 'foo',
    },
  },
});

caution

Undecided point: How should association references in WHERE behave?

The following solutions have been tagged ✅ for "technically possible" or ❌ if such a solution would be incompatible with existing options.

✅ Option 1: It only filters its own level, can only reference parent models

This is possibly The "safest" solution: no broken meaning possible

When using include, the where clause can only reference attributes from itself or its parent model.

await User.findAll({
  alias: 'user',
  include: {
    association: User.associations.projects,
    // This is perfectly valid. "user" is a parent of "projects" 
    // and can be referenced in this level.
    where: {
      'user.name': 'bar',
    },
  },
  // This will not work. The "projects" include is a descendant of "user",
  // only parent levels can be referenced.
  where: {
    'projects.name': 'foo',
  },
});

Note: You can still filter User based on the existence of a project named "foo" by using a subquery, or a join instead.

✅ Option 2: It only filters its own level, but can reference child includes

it works but its behavior may not be intuitive. It may be better to simply forbid this and tell users to use join & subqueries

The following query would only return users that have a project named "foo", but would return all projects for those users.

await User.findAll({
  alias: 'user',
  include: User.associations.projects,
  where: {
    'projects.name': 'foo',
  },
  limit: 3,
});

It would be equivalent to the following SQL:

SELECT "user".*
FROM (
  SELECT "user".* FROM "users" AS "user"
  WHERE "user"."id" IN (
    SELECT DISTINCT "projects"."user_id" FROM "projects" AS "projects"
    WHERE "projects"."name" = 'foo'
  )
  LIMIT 3
) "user"
LEFT JOIN "projects" AS "projects" ON "user"."id" = "projects"."user_id";

❌ Option 3: It only filters the associated model

This solution is not possible!

Both of the following queries would have the same behavior:

await User.findAll({
  include: User.associations.projects,
  where: {
    'projects.name': 'foo',
  },
});

await User.findAll({
  include: {
    association: User.associations.projects,
    where: {
      name: 'foo',
    },
  },
});

❌ This solution is impossible because the following query would have an impossible meaning:

await User.findAll({
  include: [User.associations.projects, User.associations.groups],
  where: or({
    // what would this even mean?
    // note: this is actually possible if you do a JOIN, 
    // but its meaning for include would be completely broken
    'projects.name': 'foo',
    'groups.name': 'bar',
  }),
});

❌ Option 4: It filters both the association AND the current level

This is the behavior in Sequelize v6, because it is what happens when you use WHERE on joined tables, but it is as broken as Option 3 (for the same reasons), and should be discarded

Take the following:

await User.findAll({
  alias: 'user',
  include: User.associations.projects,
  where: {
    'projects.name': 'foo',
  },
});

The above will return all users that have at least one project named "foo", and only its projects named "foo" will be included.

It would produce the following SQL:

SELECT "user".*
FROM "users" AS "user"
LEFT JOIN "projects" AS "projects" ON "user"."id" = "projects"."user_id"
WHERE "projects"."name" = 'foo';

---

A version with limits (which cannot use a simple join)

await User.findAll({
  alias: 'user',
  include: User.associations.projects,
  where: {
    'projects.name': 'foo',
  },
  limit: 2,
});

would produce the following SQL:

SELECT "user".*
FROM (
    -- limit to 2 users that have a project named "foo"
    SELECT "user".*
    FROM "users" AS "user"
    LEFT JOIN "projects" AS "projects" ON "user"."id" = "projects"."user_id"
    WHERE "projects"."name" = 'foo'
    LIMIT 2
) AS "user"
LEFT JOIN "projects" AS "projects" ON "user"."id" = "projects"."user_id"
WHERE "projects"."name" = 'foo';

---

Limiting both the top level model and the association:

await User.findAll({
  alias: 'user',
  include: { association: User.associations.projects, limit: 7 },
  where: {
    'projects.name': 'foo',
  },
  limit: 2,
});

would produce this SQL:

SELECT "user".*
FROM (
    -- limit to 2 users that have a project named "foo"
    SELECT "user".*
    FROM "users" AS "user"
    LEFT JOIN "projects" AS "projects" ON "user"."id" = "projects"."user_id"
    WHERE "projects"."name" = 'foo'
    LIMIT 2
) AS "user"
LEFT JOIN LATERAL (
    -- limit to 7 projects named "foo" per user
    SELECT "projects".*
    FROM "projects" AS "projects"
    WHERE "projects"."user_id" = "user"."id"
    AND "projects"."name" = 'foo'
    LIMIT 7
) AS "projects" ON true;

❌ This solution should be discarded because the following query would have a completely broken meaning:

await User.findAll({
  include: [User.associations.projects, User.associations.groups],
  where: or({
    // what would this even mean?
    // note: this is actually possible if you do a JOIN, 
    // but its meaning for include would be completely broken
    'projects.name': 'foo',
    'groups.name': 'bar',
  }),
});

Filtering based on an association's existence

include supports the required option, which ensures at least one associated entity exists and matches the WHERE clause for returned each parent entity. If no associated entity matches the WHERE clause, the parent entity will not be included in the result.

await User.findAll({
  include: {
    association: User.associations.projects,
    // Only users that have at least one project whose title is "My Title" will be returned.
    required: true,
    where: {
      // Only projects with the title "My Title" will be included.
      title: "My Title",
    },
  },
});

Note that in the above example, only projects with the title "My Title" will be included in the result. If you want to filter the list of users based on the name of its projects, but still want to include all projects, you can do either of the following:

1. Use a subquery to filter the list of users based on the name of its projects, and use include to fetch all projects for each user.
2. Use a required include to filter the list of users, and a non-required include to fetch all projects for each user.
   This solution is easier than a subquery, but less efficient due to extra data being fetched.

   await User.findAll({
     include: [{
       association: User.associations.projects,
       // If an association is included more than once, it must be aliased to avoid name collisions.
       alias: 'projects-filter',
       // Only users that have at least one project whose title is "My Title" will be returned.
       required: true,
       where: {
         title: 'My Title',
       },
     }, {
       association: User.associations.projects,
       // All projects will be included
       required: false,
     }],
   });

Fetching includes in separate queries

Selecting many includes at once can result in both a complex query and a large amount of data being fetched from the database at once (due to the returned rows being the cartesian product of all included associations).

To avoid this, you can use the separate option to fetch the associated entities in a subsequent query.

await User.findAll({
  limit: 3,
  include: {
    association: User.associations.projects,
    separate: true,
    limit: 2,
    include: {
      association: Project.associations.tasks,
      limit: 12,
    },
  },
});

Keep in mind that the associated entities will only be fetched after the main query has been executed.

A limitation of this approach is that you cannot reference the parent entity from the include option, since it won't be available in the subsequent query:

await User.findAll({
  alias: 'user',
  include: {
    association: User.associations.projects,
    separate: true,
    include: {
      association: Project.associations.tasks,
      // This would work without the "separate" option, but won't work with it.
      where: { assignedId: col('user.id') },
    },
  },
});