如何在NodeJS Sequelize中通过查询对组进行计数 [英] How to count a group by query in NodeJS Sequelize
本文介绍了如何在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.
推荐答案
此sequelize 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屋!
查看全文