如何在 NodeJS Sequelize 中按查询计算组 [英] How to count a group by query in NodeJS Sequelize

问题描述

在 Rails 中，我可以执行一个简单的 ORM 查询来查询模型拥有的点赞数:

In Rails I can perform a simple ORM query for the number of Likes a model has:

    @records = Model
        .select( 'model.*' )
        .select( 'count(likes.*) as likes_count' )
        .joins( 'LEFT JOIN likes ON model.id = likes.model_id' )
        .group( 'model.id' )

这会生成查询:

SELECT  models.*, count(likes.*) as likes_count
FROM "models" JOIN likes ON models.id = likes.model_id
GROUP BY models.id

在 Node Sequelize 中，任何类似的尝试都会失败:

In Node Sequelize, any attempt at doing something similar fails:

return Model.findAll({
    group: [ '"Model".id' ],
    attributes: ['id', [Sequelize.fn('count', Sequelize.col('"Likes".id')), 'likes_count']],
    include: [{ attributes: [], model: Like }],
});

这会生成查询:

SELECT
    Model.id,
    count(Likes.id) AS likes_count,
    Likes.id AS Likes.id           # Bad!
FROM Models AS Model
LEFT OUTER JOIN Likes
    AS Likes
    ON Model.id = Likes.model_id
GROUP BY Model.id;

哪个会产生错误:

column "Likes.id" must appear in the GROUP BY clause or be used in an aggregate function

它错误地选择了likes.id，我不知道为什么，也不知道如何摆脱它.

It's erroneously selecting likes.id, and I have no idea why, nor how to get rid of it.

推荐答案

这个续集github问题看起来完全像你的情况:

This sequelize github issue looks totally like your case:

User.findAll({
  attributes: ['User.*', 'Post.*', [sequelize.fn('COUNT', 'Post.id'), 'PostCount']],
  include: [Post]
});

这篇关于如何在 NodeJS Sequelize 中按查询计算组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！