时间戳

首页 > 解决方案 > sequelize mysql中的嵌套查询以获取赞成票和反对票数

mysql - sequelize mysql中的嵌套查询以获取赞成票和反对票数

问题描述

我正在尝试对具有关联的答案表和与答案关联的单独投票表的帖子进行投票和否决

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
            }
        },]

标签： mysqlnode.jsexpresssequelize.js

解决方案

您可以尝试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"]
          ] 
          }
        }]
    }]
});

推荐阅读