javascript - Sequelize : Add attribute column to query with where condition?

Question

我有两个模型，Posts 和 PostLikes。 PostLikes 包含一个 Username 列，该列与喜欢 Post the Like 关联的用户相关联。

我想查询所有帖子，但添加一个属性，该属性可以找到任何带有我可以动态传入的用户名的 PostLike。

例如:

  const { Username } = req.data 

  const posts = await PostModel.findAll({
  attributes: [
    "id",
    "Username",
    "Title",
    "Body",
    "createdAt",
    "updatedAt",
    [sequelize.fn("COUNT", sequelize.col("PostLikes.PostId")), "LikeCount"],
    [
      sequelize.fn("COUNT", sequelize.col("PostDislikes.PostId")),
      "DislikeCount",
    ],
    [sequelize.fn("COUNT", sequelize.col("PostLikes.Username")), "Liked"], // Right here I would like to see if there is a record in PostLikes for the Post.id, that equals a Username that I pass in.
  ],
  include: [
    {
      model: PostLikeModel,
      attributes: ["Username"],
    },
    {
      model: PostDislikeModel,
      attributes: [],
    },
  ],
  group: ["Post.id", "PostLikes.id"],
  order: [["createdAt", "DESC"]],
});

无论如何要传入 Username 变量以将属性添加到找到的每个帖子？我不确定我的措辞是否正确。 Sequelize 是一种痛苦的工作。

编辑:我尝试添加为文字:

    [
      sequelize.literal(
        `(SELECT * FROM "PostLikes" WHERE "Username" = ${Username})`
      ),
      "Liked",
    ],

也许这是进步，因为我现在收到的错误是:

UnhandledPromiseRejectionWarning: SequelizeDatabaseError: column "testaccount" does not exist

Answer 1

您可以在包含中使用 where 子句，就像您可以在 findAll 中使用它一样。如果我正确理解你的问题，这将是要走的路。

但请记住，这会将您的包含查询从左连接更改为内连接；这意味着它只会获取具有 PostLike 的 Post。

根据您的需要，您可以像我在下面的示例中那样传递 required: false 属性。如果不需要，您可以将其删除

const posts = await PostModel.findAll({
  attributes: [
    "id",
    "Username",
    "Title",
    "Body",
    "createdAt",
    "updatedAt",
    [sequelize.fn("COUNT", sequelize.col("PostLikes.PostId")), "LikeCount"],
    [
      sequelize.fn("COUNT", sequelize.col("PostDislikes.PostId")),
      "DislikeCount",
    ],
    [sequelize.fn("COUNT", sequelize.col("PostLikes.Username")), "Liked"], // Right here I would like to see if there is a record in PostLikes for the Post.id, that equals a Username that I pass in.
  ],
  include: [
    {
      model: PostLikeModel,
      attributes: ["Username"],
      where: {Username: 'YourValueHere'},
      required: false
    },
    {
      model: PostDislikeModel,
      attributes: [],
    },
  ],
  group: ["Post.id", "PostLikes.id"],
  order: [["createdAt", "DESC"]],
});