postgresql Sequelize：向带有where条件的查询添加属性列？

t30tvxxf 于 2023-10-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(113)

我有两个模型，Posts和PostLikes。PostLikes包含一个与用户相关联的用户列，该用户喜欢与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"]],
});

是否有任何方法可以传入一个变量来为找到的每个帖子添加属性？我不确定我的措辞是否正确。续集是一个痛苦的工作。
编辑：我尝试添加文字：

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

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

UnhandledPromiseRejectionWarning: SequelizeDatabaseError: column "testaccount" does not exist

postgresql

来源：https://stackoverflow.com/questions/61875946/sequelize-add-attribute-column-to-query-with-where-condition

2条答案

按热度按时间

f8rj6qna1#

你可以在include中使用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"]],
});

赞(0）回复(0）举报 2023-10-18

djmepvbi2#

从以下位置更新您的文字：

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

收件人：

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

赞(0）回复(0）举报 2023-10-18

我来回答

postgresql Sequelize：向带有where条件的查询添加属性列？

2条答案

相关问题

热门标签

最新问答