mysql - sequelize mysql 中的嵌套查询以获取 upvote 和 downvote 计数

Question

我正在尝试为 帖子 获得赞成和反对票，其中 答案 表关联和单独的 投票 表关联到 答案

const posts = await Posts.findAll({
    include: [{
        model: Answers,
        include: [{
          attributes: { 
              include: [[Sequelize.fn("COUNT", Sequelize.col("id")), "votesCount"]] 
          },
          model: Votes, attributes: []
        }]
    }]
});

这是我的 投票 架构

const Votes = sequelize.define('votes', {
    id:{
        type:Sequelize.INTEGER,
        autoIncrement:true,
        allowNull:false,
        primaryKey:true
      },
      answerId: {
        type: Sequelize.INTEGER,
        allowNull:false,
         references: {
          model: 'answers',
          key: 'id'
        }
      },
      userId: {
        type: Sequelize.INTEGER,
        allowNull:false,
         references: {
          model: 'users',
          key: 'id'
        }
      },
      voteType: { type: Sequelize.BOOLEAN, allowNull:false },
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE
})

voteType true 表示赞成，false 表示反对
有没有办法用 Sequelize 获得结果？
期待这样的事情，

[{
            "id": 1,
            "userId": 15,
            "title": "The standard Lorem Ipsum passage, used since the 1500s",
            "description": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i",
            "answers": [
                {
                    "id": 1,
                    "postId": 1,
                    "userId": 33,
                    "ans": "Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old. Richard McClintock, a Latin professor at Hampden-Sydney College in Virginia",
                }
            ],
            votes: {
                upVote: 5,
                downVote: 1
            }
        },]

Answer 1

您可以尝试使用 Sequelize.literal 通过使用子查询来获取两个计数:

const posts = await Posts.findAll({
    include: [{
        model: Answers,
        include: [{
          attributes: { 
              include: [
  [Sequelize.literal("(SELECT COUNT(*) FROM Votes WHERE answerId=answer.id AND Votes.voteType=true)"), "upVote"],
  [Sequelize.literal("(SELECT COUNT(*) FROM Votes WHERE answerId=answer.id AND Votes.voteType=false)"), "downVote"]
          ] 
          }
        }]
    }]
});