子查询

假设你有两个模型 Post 和 Reaction，并且设置了一对多关系，因此一篇帖子有很多反应：

¥Consider you have two models, Post and Reaction, with a One-to-Many relationship set up, so that one post has many reactions:

const Post = sequelize.define(
  'post',
  {
    content: DataTypes.STRING,
  },
  { timestamps: false },
);

const Reaction = sequelize.define(
  'reaction',
  {
    type: DataTypes.STRING,
  },
  { timestamps: false },
);

Post.hasMany(Reaction);
Reaction.belongsTo(Post);

注意：我们禁用了时间戳，只是为了对下一个示例进行更短的查询。

¥Note: we have disabled timestamps just to have shorter queries for the next examples.

让我们用一些数据填充表格：

¥Let's fill our tables with some data:

async function makePostWithReactions(content, reactionTypes) {
  const post = await Post.create({ content });
  await Reaction.bulkCreate(reactionTypes.map(type => ({ type, postId: post.id })));
  return post;
}

await makePostWithReactions('Hello World', [
  'Like',
  'Angry',
  'Laugh',
  'Like',
  'Like',
  'Angry',
  'Sad',
  'Like',
]);
await makePostWithReactions('My Second Post', ['Laugh', 'Laugh', 'Like', 'Laugh']);

现在，我们准备好通过示例来展示子查询的强大功能。

¥Now, we are ready for examples of the power of subqueries.

假设我们想通过 SQL 为每个帖子计算 laughReactionsCount。我们可以通过子查询来实现这一点，如下所示：

¥Let's say we wanted to compute via SQL a laughReactionsCount for each post. We can achieve that with a sub-query, such as the following:

SELECT
    *,
    (
        SELECT COUNT(*)
        FROM reactions AS reaction
        WHERE
            reaction.postId = post.id
            AND
            reaction.type = "Laugh"
    ) AS laughReactionsCount
FROM posts AS post

如果我们通过 Sequelize 运行上述原始 SQL 查询，我们会得到：

¥If we run the above raw SQL query through Sequelize, we get:

[
  {
    "id": 1,
    "content": "Hello World",
    "laughReactionsCount": 1
  },
  {
    "id": 2,
    "content": "My Second Post",
    "laughReactionsCount": 3
  }
]

那么，我们如何才能在 Sequelize 的更多帮助下实现这一目标，而不必手动编写整个原始查询呢？

¥So how can we achieve that with more help from Sequelize, without having to write the whole raw query by hand?

答案：通过将查找器方法（例如 findAll）的 attributes 选项与 sequelize.literal 实用函数相结合，允许你直接将任意内容插入到查询中，而无需任何自动转义。

¥The answer: by combining the attributes option of the finder methods (such as findAll) with the sequelize.literal utility function, that allows you to directly insert arbitrary content into the query without any automatic escaping.

这意味着 Sequelize 将帮助你完成主要的、更大的查询，但你仍然需要自己编写该子查询：

¥This means that Sequelize will help you with the main, larger query, but you will still have to write that sub-query by yourself:

Post.findAll({
  attributes: {
    include: [
      [
        // Note the wrapping parentheses in the call below!
        sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM reactions AS reaction
                    WHERE
                        reaction.postId = post.id
                        AND
                        reaction.type = "Laugh"
                )`),
        'laughReactionsCount',
      ],
    ],
  },
});

重要的提示：由于 sequelize.literal 插入任意内容而不转义查询，因此值得特别关注，因为它可能是（主要）安全漏洞的来源。它不应该用于用户生成的内容。然而，在这里，我们使用的是 sequelize.literal 和一个由我们（编码人员）精心编写的固定字符串。这没关系，因为我们知道我们在做什么。

¥Important Note: Since sequelize.literal inserts arbitrary content without escaping to the query, it deserves very special attention since it may be a source of (major) security vulnerabilities. It should not be used on user-generated content. However, here, we are using sequelize.literal with a fixed string, carefully written by us (the coders). This is ok, since we know what we are doing.

上面给出了以下输出：

¥The above gives the following output:

[
  {
    "id": 1,
    "content": "Hello World",
    "laughReactionsCount": 1
  },
  {
    "id": 2,
    "content": "My Second Post",
    "laughReactionsCount": 3
  }
]

成功！

¥Success!

使用子查询进行复杂排序

¥Using sub-queries for complex ordering

这个想法可用于实现复杂的排序，例如根据帖子的笑反应数量对帖子进行排序：

¥This idea can be used to enable complex ordering, such as ordering posts by the number of laugh reactions they have:

Post.findAll({
  attributes: {
    include: [
      [
        sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM reactions AS reaction
                    WHERE
                        reaction.postId = post.id
                        AND
                        reaction.type = "Laugh"
                )`),
        'laughReactionsCount',
      ],
    ],
  },
  order: [[sequelize.literal('laughReactionsCount'), 'DESC']],
});

结果：

¥Result:

[
  {
    "id": 2,
    "content": "My Second Post",
    "laughReactionsCount": 3
  },
  {
    "id": 1,
    "content": "Hello World",
    "laughReactionsCount": 1
  }
]