使用sql/sequelize，通过userid获得喜欢的帖子，并获得帖子的喜欢次数

wko9yo5t 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(323)

你能帮我回答这个问题吗。我需要查询所有的职位，是由用户喜欢与'userid'。此外，我需要'喜欢计数'这个职位包括在属性。

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）的帖子，那个帖子放在了当前用户身上！所以相似的计数是错误的(

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

我在这篇文章上只得到了我的后React，但是还有很多其他的React！

sql node.js sequelize.js

来源：https://stackoverflow.com/questions/62388859/getting-liked-posts-by-userid-and-getting-count-of-posts-likes-using-sql-seque

1条答案

按热度按时间

q3qa4bjr1#

小时ıı, 我有相似的建筑。我在分享我的问题，也许对你有好处

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);
    }
},

赞(0）回复(0）举报 2021-07-26

我来回答

使用sql/sequelize，通过userid获得喜欢的帖子，并获得帖子的喜欢次数

1条答案

相关问题

热门标签

最新问答