预加载

正如 关联指南 中简要提到的，Eager Loading 是一次查询多个模型（一个 'main' 模型和一个或多个关联模型）的数据的行为。在 SQL 级别，这是一个包含一个或多个 joins 的查询。

¥As briefly mentioned in the associations guide, eager Loading is the act of querying data of several models at once (one 'main' model and one or more associated models). At the SQL level, this is a query with one or more joins.

完成此操作后，关联的模型将由 Sequelize 添加到返回对象中适当命名的自动创建的字段中。

¥When this is done, the associated models will be added by Sequelize in appropriately named, automatically created field(s) in the returned objects.

在 Sequelize 中，预加载主要是通过在模型查找器查询上使用 include 选项（例如 findOne、findAll 等）来完成。

¥In Sequelize, eager loading is mainly done by using the include option on a model finder query (such as findOne, findAll, etc).

基本示例

¥Basic example

我们假设以下设置：

¥Let's assume the following setup:

const User = sequelize.define('user', { name: DataTypes.STRING }, { timestamps: false });
const Task = sequelize.define('task', { name: DataTypes.STRING }, { timestamps: false });
const Tool = sequelize.define(
  'tool',
  {
    name: DataTypes.STRING,
    size: DataTypes.STRING,
  },
  { timestamps: false },
);
User.hasMany(Task);
Task.belongsTo(User);
User.hasMany(Tool, { as: 'Instruments' });

获取单个关联元素

¥Fetching a single associated element

好的。因此，首先，让我们加载所有任务及其关联的用户：

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

const tasks = await Task.findAll({ include: User });
console.log(JSON.stringify(tasks, null, 2));

输出：

¥Output:

[
  {
    "name": "A Task",
    "id": 1,
    "userId": 1,
    "user": {
      "name": "John Doe",
      "id": 1
    }
  }
]

这里，tasks[0].user instanceof User 是 true。这表明，当 Sequelize 获取关联模型时，它们会作为模型实例添加到输出对象中。

¥Here, tasks[0].user instanceof User is true. This shows that when Sequelize fetches associated models, they are added to the output object as model instances.

上面，关联的模型已添加到提取任务中名为 user 的新字段中。此字段的名称由 Sequelize 根据关联模型的名称自动选择，在适用时（即关联为 hasMany 或 belongsToMany 时）使用其复数形式。换句话说，从 Task.belongsTo(User) 开始，一项任务与一个用户相关联，因此逻辑选择是单数形式（Sequelize 自动遵循）。

¥Above, the associated model was added to a new field called user in the fetched task. The name of this field was automatically chosen by Sequelize based on the name of the associated model, where its pluralized form is used when applicable (i.e., when the association is hasMany or belongsToMany). In other words, since Task.belongsTo(User), a task is associated to one user, therefore the logical choice is the singular form (which Sequelize follows automatically).

获取所有关联元素

¥Fetching all associated elements

现在，我们将执行相反的操作，而不是加载与给定任务关联的用户 - 我们将找到与给定用户关联的所有任务。

¥Now, instead of loading the user that is associated to a given task, we will do the opposite - we will find all tasks associated to a given user.

方法调用本质上是一样的。唯一的区别是，现在在查询结果中创建的额外字段使用复数形式（本例中为 tasks），并且其值是任务实例的数组（而不是如上所述的单个实例）。

¥The method call is essentially the same. The only difference is that now the extra field created in the query result uses the pluralized form (tasks in this case), and its value is an array of task instances (instead of a single instance, as above).

const users = await User.findAll({ include: Task });
console.log(JSON.stringify(users, null, 2));

输出：

¥Output:

[
  {
    "name": "John Doe",
    "id": 1,
    "tasks": [
      {
        "name": "A Task",
        "id": 1,
        "userId": 1
      }
    ]
  }
]

请注意，访问器（结果实例中的 tasks 属性）是复数的，因为关联是一对多的。

¥Notice that the accessor (the tasks property in the resulting instance) is pluralized since the association is one-to-many.

获取别名关联

¥Fetching an Aliased association

如果关联有别名（使用 as 选项），则在包含模型时必须指定此别名。你不应该将模型直接传递给 include 选项，而应该提供一个具有两个选项的对象：model 和 as。

¥If an association is aliased (using the as option), you must specify this alias when including the model. Instead of passing the model directly to the include option, you should instead provide an object with two options: model and as.

请注意上面用户的 Tool 是如何别名为 Instruments 的。为了做到这一点，你必须指定要加载的模型以及别名：

¥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:

const users = await User.findAll({
  include: { model: Tool, as: 'Instruments' },
});
console.log(JSON.stringify(users, null, 2));

输出：

¥Output:

[
  {
    "name": "John Doe",
    "id": 1,
    "Instruments": [
      {
        "name": "Scissor",
        "id": 1,
        "userId": 1
      }
    ]
  }
]

你还可以通过指定与关联别名匹配的字符串来包含别名：

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

User.findAll({ include: 'Instruments' }); // Also works
User.findAll({ include: { association: 'Instruments' } }); // Also works

需要预加载

¥Required eager loading

当预加载时，我们可以强制查询仅返回具有关联模型的记录，从而有效地将查询从默认的 OUTER JOIN 转换为 INNER JOIN。这是通过 required: true 选项完成的，如下所示：

¥When eager loading, we can force the query to return only records which have an associated model, effectively converting the query from the default OUTER JOIN to an INNER JOIN. This is done with the required: true option, as follows:

User.findAll({
  include: {
    model: Task,
    required: true,
  },
});

此选项也适用于嵌套包含。

¥This option also works on nested includes.

在关联模型级别过滤预加载

¥Eager loading filtered at the associated model level

当预加载时，我们还可以使用 where 选项过滤关联的模型，如下例所示：

¥When eager loading, we can also filter the associated model using the where option, as in the following example:

User.findAll({
  include: {
    model: Tool,
    as: 'Instruments',
    where: {
      size: {
        [Op.ne]: 'small',
      },
    },
  },
});

生成的 SQL：

¥Generated SQL:

SELECT
  `user`.`id`,
  `user`.`name`,
  `Instruments`.`id` AS `Instruments.id`,
  `Instruments`.`name` AS `Instruments.name`,
  `Instruments`.`size` AS `Instruments.size`,
  `Instruments`.`userId` AS `Instruments.userId`
FROM `users` AS `user`
INNER JOIN `tools` AS `Instruments` ON
  `user`.`id` = `Instruments`.`userId` AND
  `Instruments`.`size` != 'small';

请注意，上面生成的 SQL 查询将仅获取至少拥有一种与条件匹配的工具（在本例中不是 small）的用户。出现这种情况是因为，当 where 选项在 include 内部使用时，Sequelize 会自动将 required 选项设置为 true。这意味着，完成的不是 OUTER JOIN，而是 INNER JOIN，仅返回具有至少一个匹配子项的父模型。

¥Note that the SQL query generated above will only fetch users that have at least one tool that matches the condition (of not being small, in this case). This is the case because, when the where option is used inside an include, Sequelize automatically sets the required option to true. This means that, instead of an OUTER JOIN, an INNER JOIN is done, returning only the parent models with at least one matching children.

另请注意，使用的 where 选项已转换为 INNER JOIN 的 ON 子句的条件。为了获得顶层 WHERE 子句，而不是 ON 子句，必须做一些不同的事情。接下来将展示这一点。

¥Note also that the where option used was converted into a condition for the ON clause of the INNER JOIN. In order to obtain a top-level WHERE clause, instead of an ON clause, something different must be done. This will be shown next.

参考其他栏目

¥Referring to other columns

如果要在引用关联模型中的值的包含模型中应用 WHERE 子句，则可以简单地使用 Sequelize.col 函数，如下例所示：

¥If you want to apply a WHERE clause in an included model referring to a value from an associated model, you can simply use the Sequelize.col function, as show in the example below:

// Find all projects with a least one task where task.state === project.state
Project.findAll({
  include: {
    model: Task,
    where: {
      state: Sequelize.col('project.state'),
    },
  },
});

顶层的复杂 where 子句

¥Complex where clauses at the top-level

为了获取涉及嵌套列的顶层 WHERE 子句，Sequelize 提供了一种引用嵌套列的方法：'$nested.column$' 语法。

¥To obtain top-level WHERE clauses that involve nested columns, Sequelize provides a way to reference nested columns: the '$nested.column$' syntax.

例如，它可用于将 where 条件从包含的模型中的 ON 条件移动到顶层 WHERE 子句。

¥It can be used, for example, to move the where conditions from an included model from the ON condition to a top-level WHERE clause.

User.findAll({
  where: {
    '$Instruments.size$': { [Op.ne]: 'small' },
  },
  include: [
    {
      model: Tool,
      as: 'Instruments',
    },
  ],
});

生成的 SQL：

¥Generated SQL:

SELECT
  `user`.`id`,
  `user`.`name`,
  `Instruments`.`id` AS `Instruments.id`,
  `Instruments`.`name` AS `Instruments.name`,
  `Instruments`.`size` AS `Instruments.size`,
  `Instruments`.`userId` AS `Instruments.userId`
FROM `users` AS `user`
LEFT OUTER JOIN `tools` AS `Instruments` ON
  `user`.`id` = `Instruments`.`userId`
WHERE `Instruments`.`size` != 'small';

$nested.column$ 语法也适用于嵌套多层的列，例如 $some.super.deeply.nested.column$。因此，你可以使用它在深度嵌套的列上创建复杂的过滤器。

¥The $nested.column$ syntax also works for columns that are nested several levels deep, such as $some.super.deeply.nested.column$. Therefore, you can use this to make complex filters on deeply nested columns.

为了更好地理解内部 where 选项（在 include 内部使用）、带或不带 required 选项以及使用 $nested.column$ 语法的顶层 where 之间的所有差异，下面我们为你提供了四个示例：

¥For a better understanding of all differences between the inner where option (used inside an include), with and without the required option, and a top-level where using the $nested.column$ syntax, below we have four examples for you:

// Inner where, with default `required: true`
await User.findAll({
  include: {
    model: Tool,
    as: 'Instruments',
    where: {
      size: { [Op.ne]: 'small' },
    },
  },
});

// Inner where, `required: false`
await User.findAll({
  include: {
    model: Tool,
    as: 'Instruments',
    where: {
      size: { [Op.ne]: 'small' },
    },
    required: false,
  },
});

// Top-level where, with default `required: false`
await User.findAll({
  where: {
    '$Instruments.size$': { [Op.ne]: 'small' },
  },
  include: {
    model: Tool,
    as: 'Instruments',
  },
});

// Top-level where, `required: true`
await User.findAll({
  where: {
    '$Instruments.size$': { [Op.ne]: 'small' },
  },
  include: {
    model: Tool,
    as: 'Instruments',
    required: true,
  },
});

生成的 SQL，按顺序：

¥Generated SQLs, in order:

-- Inner where, with default `required: true`
SELECT [...] FROM `users` AS `user`
INNER JOIN `tools` AS `Instruments` ON
  `user`.`id` = `Instruments`.`userId`
  AND `Instruments`.`size` != 'small';

-- Inner where, `required: false`
SELECT [...] FROM `users` AS `user`
LEFT OUTER JOIN `tools` AS `Instruments` ON
  `user`.`id` = `Instruments`.`userId`
  AND `Instruments`.`size` != 'small';

-- Top-level where, with default `required: false`
SELECT [...] FROM `users` AS `user`
LEFT OUTER JOIN `tools` AS `Instruments` ON
  `user`.`id` = `Instruments`.`userId`
WHERE `Instruments`.`size` != 'small';

-- Top-level where, `required: true`
SELECT [...] FROM `users` AS `user`
INNER JOIN `tools` AS `Instruments` ON
  `user`.`id` = `Instruments`.`userId`
WHERE `Instruments`.`size` != 'small';

使用 RIGHT OUTER JOIN 获取（仅限 MySQL、MariaDB、PostgreSQL 和 MSSQL）

¥Fetching with RIGHT OUTER JOIN (MySQL, MariaDB, PostgreSQL and MSSQL only)

默认情况下，关联使用 LEFT OUTER JOIN 加载 - 也就是说，它只包含父表中的记录。如果你使用的方言支持，你可以通过传递 right 选项将此行为更改为 RIGHT OUTER JOIN。

¥By default, associations are loaded using a LEFT OUTER JOIN - that is to say it only includes records from the parent table. You can change this behavior to a RIGHT OUTER JOIN by passing the right option, if the dialect you are using supports it.

目前，SQLite 不支持 右连接。

¥Currently, SQLite does not support right joins.

注意：仅当 required 为 false 时，才会尊重 right。

¥Note: right is only respected if required is false.

User.findAll({
  include: [{
    model: Task // will create a left join
  }]
});
User.findAll({
  include: [{
    model: Task,
    right: true // will create a right join
  }]
});
User.findAll({
  include: [{
    model: Task,
    required: true,
    right: true // has no effect, will create an inner join
  }]
});
User.findAll({
  include: [{
    model: Task,
    where: { name: { [Op.ne]: 'empty trash' } },
    right: true // has no effect, will create an inner join
  }]
});
User.findAll({
  include: [{
    model: Tool,
    where: { name: { [Op.ne]: 'empty trash' } },
    required: false // will create a left join
  }]
});
User.findAll({
  include: [{
    model: Tool,
    where: { name: { [Op.ne]: 'empty trash' } },
    required: false
    right: true // will create a right join
  }]
});

多次预加载

¥Multiple eager loading

include 选项可以接收一个数组，以便一次获取多个关联模型：

¥The include option can receive an array in order to fetch multiple associated models at once:

Foo.findAll({
  include: [
    {
      model: Bar,
      required: true
    },
    {
      model: Baz,
      where: /* ... */
    },
    Qux // Shorthand syntax for { model: Qux } also works here
  ]
})

具有多对多关系的预加载

¥Eager loading with Many-to-Many relationships

当你对具有“属于多多”关系的模型执行预先加载时，默认情况下，Sequelize 也会获取联结表数据。例如：

¥When you perform eager loading on a model with a Belongs-to-Many relationship, Sequelize will fetch the junction table data as well, by default. For example:

const Foo = sequelize.define('Foo', { name: DataTypes.TEXT });
const Bar = sequelize.define('Bar', { name: DataTypes.TEXT });
Foo.belongsToMany(Bar, { through: 'Foo_Bar' });
Bar.belongsToMany(Foo, { through: 'Foo_Bar' });

await sequelize.sync();
const foo = await Foo.create({ name: 'foo' });
const bar = await Bar.create({ name: 'bar' });
await foo.addBar(bar);
const fetchedFoo = await Foo.findOne({ include: Bar });
console.log(JSON.stringify(fetchedFoo, null, 2));

输出：

¥Output:

{
  "id": 1,
  "name": "foo",
  "Bars": [
    {
      "id": 1,
      "name": "bar",
      "Foo_Bar": {
        "FooId": 1,
        "BarId": 1
      }
    }
  ]
}

请注意，每个预加载到 "Bars" 属性中的 bar 实例都有一个名为 Foo_Bar 的额外属性，它是连接模型的相关 Sequelize 实例。默认情况下，Sequelize 从联结表中获取所有属性以构建此额外属性。

¥Note that every bar instance eager loaded into the "Bars" property has an extra property called Foo_Bar which is the relevant Sequelize instance of the junction model. By default, Sequelize fetches all attributes from the junction table in order to build this extra property.

但是，你可以指定要获取哪些属性。这是通过在包含的 through 选项中应用 attributes 选项来完成的。例如：

¥However, you can specify which attributes you want fetched. This is done with the attributes option applied inside the through option of the include. For example:

Foo.findAll({
  include: [
    {
      model: Bar,
      through: {
        attributes: [
          /* list the wanted attributes here */
        ],
      },
    },
  ],
});

如果你不需要联结表中的任何内容，则可以在 include 选项的 through 选项内向 attributes 选项显式提供一个空数组，在这种情况下，不会获取任何内容，甚至不会创建额外的属性：

¥If you don't want anything from the junction table, you can explicitly provide an empty array to the attributes option inside the through option of the include option, and in this case nothing will be fetched and the extra property will not even be created:

Foo.findOne({
  include: {
    model: Bar,
    through: {
      attributes: [],
    },
  },
});

输出：

¥Output:

{
  "id": 1,
  "name": "foo",
  "Bars": [
    {
      "id": 1,
      "name": "bar"
    }
  ]
}

每当包含多对多关系中的模型时，你还可以在联结表上应用过滤器。这是通过在包含的 through 选项中应用 where 选项来完成的。例如：

¥Whenever including a model from a Many-to-Many relationship, you can also apply a filter on the junction table. This is done with the where option applied inside the through option of the include. For example:

User.findAll({
  include: [
    {
      model: Project,
      through: {
        where: {
          // Here, `completed` is a column present at the junction table
          completed: true,
        },
      },
    },
  ],
});

生成的 SQL（使用 SQLite）：

¥Generated SQL (using SQLite):

SELECT
  `User`.`id`,
  `User`.`name`,
  `Projects`.`id` AS `Projects.id`,
  `Projects`.`name` AS `Projects.name`,
  `Projects->User_Project`.`completed` AS `Projects.User_Project.completed`,
  `Projects->User_Project`.`UserId` AS `Projects.User_Project.UserId`,
  `Projects->User_Project`.`ProjectId` AS `Projects.User_Project.ProjectId`
FROM `Users` AS `User`
LEFT OUTER JOIN `User_Projects` AS `Projects->User_Project` ON
  `User`.`id` = `Projects->User_Project`.`UserId`
LEFT OUTER JOIN `Projects` AS `Projects` ON
  `Projects`.`id` = `Projects->User_Project`.`ProjectId` AND
  `Projects->User_Project`.`completed` = 1;

包括一切

¥Including everything

要包含所有关联模型，你可以使用 all 和 nested 选项：

¥To include all associated models, you can use the all and nested options:

// Fetch all models associated with User
User.findAll({ include: { all: true } });

// Fetch all models associated with User and their nested associations (recursively)
User.findAll({ include: { all: true, nested: true } });

包括软删除记录

¥Including soft deleted records

如果你想立即加载软删除的记录，可以通过将 include.paranoid 设置为 false 来实现：

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

User.findAll({
  include: [
    {
      model: Tool,
      as: 'Instruments',
      where: { size: { [Op.ne]: 'small' } },
      paranoid: false,
    },
  ],
});

对预加载的关联进行排序

¥Ordering eager loaded associations

当你想要将 ORDER 子句应用于预加载的模型时，必须将顶层 order 选项与增强数组一起使用，从要排序的嵌套模型的规范开始。

¥When you want to apply ORDER clauses to eager loaded models, you must use the top-level order option with augmented arrays, starting with the specification of the nested model you want to sort.

通过示例可以更好地理解这一点。

¥This is better understood with examples.

Company.findAll({
  include: Division,
  order: [
    // We start the order array with the model we want to sort
    [Division, 'name', 'ASC'],
  ],
});
Company.findAll({
  include: Division,
  order: [[Division, 'name', 'DESC']],
});
Company.findAll({
  // If the include uses an alias...
  include: { model: Division, as: 'Div' },
  order: [
    // ...we use the same syntax from the include
    // in the beginning of the order array
    [{ model: Division, as: 'Div' }, 'name', 'DESC'],
  ],
});

Company.findAll({
  // If we have includes nested in several levels...
  include: {
    model: Division,
    include: Department,
  },
  order: [
    // ... we replicate the include chain of interest
    // at the beginning of the order array
    [Division, Department, 'name', 'DESC'],
  ],
});

对于多对多关系，你还可以按直通表中的属性进行排序。例如，假设 Division 和 Department 之间有多对多关系，其结点模型为 DepartmentDivision，你可以执行以下操作：

¥In the case of many-to-many relationships, you are also able to sort by attributes in the through table. For example, assuming we have a Many-to-Many relationship between Division and Department whose junction model is DepartmentDivision, you can do:

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

在上述所有示例中，你已经注意到 order 选项是在顶层使用的。order 也在 include 选项中起作用的唯一情况是使用 separate: true 时。在这种情况下，用法如下：

¥In all the above examples, you have noticed that the order option is used at the top-level. The only situation in which order also works inside the include option is when separate: true is used. In that case, the usage is as follows:

// This only works for `separate: true` (which in turn
// only works for has-many relationships).
User.findAll({
  include: {
    model: Post,
    separate: true,
    order: [['createdAt', 'DESC']],
  },
});

涉及子查询的复杂排序

¥Complex ordering involving sub-queries

查看 子查询指南，了解如何使用子查询来协助更复杂的排序的示例。

¥Take a look at the guide on sub-queries for an example of how to use a sub-query to assist a more complex ordering.

嵌套预加载

¥Nested eager loading

你可以使用嵌套预加载来加载相关模型的所有相关模型：

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

const users = await User.findAll({
  include: {
    model: Tool,
    as: 'Instruments',
    include: {
      model: Teacher,
      include: [
        /* etc */
      ],
    },
  },
});
console.log(JSON.stringify(users, null, 2));

输出：

¥Output:

[
  {
    "name": "John Doe",
    "id": 1,
    "Instruments": [
      {
        // 1:M and N:M association
        "name": "Scissor",
        "id": 1,
        "userId": 1,
        "Teacher": {
          // 1:1 association
          "name": "Jimi Hendrix"
        }
      }
    ]
  }
]

这将产生一个外连接。但是，相关模型上的 where 子句将创建内部联接并仅返回具有匹配子模型的实例。要返回所有父实例，你应该添加 required: false。

¥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.

User.findAll({
  include: [
    {
      model: Tool,
      as: 'Instruments',
      include: [
        {
          model: Teacher,
          where: {
            school: 'Woodstock Music School',
          },
          required: false,
        },
      ],
    },
  ],
});

上面的查询将返回所有用户及其所有仪器，但仅返回与 Woodstock Music School 关联的教师。

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

将 findAndCountAll 与包含一起使用

¥Using findAndCountAll with includes

findAndCountAll 实用程序函数支持包含。count 中只会考虑标记为 required 的包含内容。例如，如果你想查找并统计拥有个人资料的所有用户：

¥The findAndCountAll utility function supports includes. Only the includes that are marked as required will be considered in count. For example, if you want to find and count all users who have a profile:

User.findAndCountAll({
  include: [{ model: Profile, required: true }],
  limit: 3,
});

由于 Profile 的包含设置了 required，因此将导致内部联接，并且只有拥有配置文件的用户才会被计算在内。如果我们从包含中删除 required，则具有和不具有配置文件的用户都将被计数。在包含中添加 where 子句会自动使其成为必需：

¥Because the include for Profile has required set it will result in an inner join, and only the users who have a profile will be counted. If we remove required from the include, both users with and without profiles will be counted. Adding a where clause to the include automatically makes it required:

User.findAndCountAll({
  include: [{ model: Profile, where: { active: true } }],
  limit: 3,
});

上面的查询将只计算拥有活动配置文件的用户，因为当你将 where 子句添加到 include 时，required 会隐式设置为 true。

¥The query above will only count users who have an active profile, because required is implicitly set to true when you add a where clause to the include.