sql - 使用 sql/sequelize 通过 userId 获取喜欢的帖子并获取帖子的喜欢计数

Question

你能帮我解决这个问题吗？我需要使用“userId”查询用户喜欢的所有帖子。另外，我需要'like_count' 包含在属性中的这个帖子。

Table "public.posts"
  Column   |           Type           | Collation | Nullable |      Default      
-----------+--------------------------+-----------+----------+-------------------
 id        | uuid                     |           | not null | gen_random_uuid()
 body      | text                     |           | not null | 
 imageId   | uuid                     |           |          | 
 userId    | uuid                     |           |          | 
 deletedAt | timestamp with time zone |           |          | 

Table "public.postReactions"
  Column   |           Type           | Collation | Nullable |      Default      
-----------+--------------------------+-----------+----------+-------------------
 id        | uuid                     |           | not null | gen_random_uuid()
 isLike    | boolean                  |           | not null | true
 userId    | uuid                     |           |          | 
 postId    | uuid                     |           |          | 

我已经完成了任务的第一部分。我得到了用户喜欢的所有帖子，但在示例中我只包含了放置当前用户的喜欢(postReactions)!所以like_count是错误的(

async getPosts(filter) {
    const {
      from: offset,
      count: limit,
      likedByUser
    } = filter;

    let where = {};

    if (likedByUser) {
      where = sequelize.where(sequelize.literal(`CASE WHEN "postReactions"."isLike" = true AND "postReactions"."userId"::text = '${likedByUser}' THEN 1 ELSE 0 END`), 1)
    }

    return this.model.findAll({
      where,
      attributes: {
        include: [
          [sequelize.fn('SUM', sequelize.literal(likeCase(true))), 'likeCount'],
          [sequelize.fn('SUM', sequelize.literal(likeCase(false))), 'dislikeCount'],
        ]
      },
      include: [{
        model: PostReactionModel,
        attributes: ['id'],
        duplicating: false,
      }],
      group: [
        'post.id',
        'postReactions.id',
      ],
      order: [['createdAt', 'DESC']],
      offset,
      limit
    });
  }

改变这部分我有类似的结果..

include: [{
        model: PostReactionModel,
        attributes: ['id'],
        **where: { userId }**
        duplicating: false,
      }],

查询结果为:

dislikeCount: "0"
id: "5c0d59ca-03e9-4aa4-829b-3642f80b721d"
likeCount: "1"
postReactions: Array(1)
0: {id: "db22c285-bef1-4abd-aad0-31bcd091ddf8"}
length: 1

W看到我在这个帖子上只有我的帖子 react ，但还有很多其他 react !

Answer 1

嗨，我有类似的架构。我正在分享我的查询，可能对您有益

productDetail: async function (req, res, next) {
    const productId = parseInt(req.params.productId);

    const getEvaluationCountQuery = function (value) {
        return `COUNT(case "evaluationGroup->evaluationAttributes->evaluations"."evaluation" when ${value} then 1 else null end)`
    }

    const product = await database.models.Product.findOne({
        where: {id: productId},
        include: [
            {
                model: database.models.EvaluationGroup,
                as: 'evaluationGroup',
                include: {
                    model: database.models.EvaluationAttribute,
                    as: 'evaluationAttributes',
                    attributes: {
                        include: [
                            [
                                database.literal(getEvaluationCountQuery(1)),
                                'upVote'
                            ],
                            [
                                database.literal(getEvaluationCountQuery(0)),
                                'downVote'
                            ],
                        ]
                    },
                    through: {
                        attributes: []
                    },
                    include: {
                        model: database.models.Evaluation,
                        as: 'evaluations',
                        where: {productId: productId},
                        attributes: [],
                        required: false,
                    },
                }
            },
        ],
        group: [
            'Product.id',
            'category.id',
            'evaluationGroup.id',
            'evaluationGroup->evaluationAttributes.id'
        ],
    });

    if (product) {
        response.ok(res, product);
    } else {
        response.error(res);
    }
},