postgresql 如何在 NodeJS Sequelize 中按查询计算一个组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29869077/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count a group by query in NodeJS Sequelize
提问by Andy Ray
In Rails I can perform a simple ORM query for the number of Likes a model has:
在 Rails 中，我可以对模型具有的点赞数执行简单的 ORM 查询：
@records = Model
.select( 'model.*' )
.select( 'count(likes.*) as likes_count' )
.joins( 'LEFT JOIN likes ON model.id = likes.model_id' )
.group( 'model.id' )
This generates the query:
这将生成查询：
SELECT models.*, count(likes.*) as likes_count
FROM "models" JOIN likes ON models.id = likes.model_id
GROUP BY models.id
In Node Sequelize, any attempt at doing something similar fails:
在 Node Sequelize 中，任何做类似事情的尝试都失败了：
return Model.findAll({
group: [ '"Model".id' ],
attributes: ['id', [Sequelize.fn('count', Sequelize.col('"Likes".id')), 'likes_count']],
include: [{ attributes: [], model: Like }],
});
This generates the query:
这将生成查询：
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;
Which generates the error:
这会产生错误：
column "Likes.id" must appear in the GROUP BY clause or be used in an aggregate function
It's erroneously selecting likes.id, and I have no idea why, nor how to get rid of it.
它错误地选择了likes.id，我不知道为什么，也不知道如何摆脱它。
回答by НЛО
This sequelize github issuelooks totally like your case:
这个 sequelize github 问题看起来完全像你的情况：
User.findAll({
attributes: ['User.*', 'Post.*', [sequelize.fn('COUNT', 'Post.id'), 'PostCount']],
include: [Post]
});
回答by sangram
To resolve this problem we Need to upgrade to latest version of sequelize and include raw = true, Here is How I had done after lot of iteration and off-course googling.
为了解决这个问题，我们需要升级到最新版本的 sequelize 并包含 raw = true，这是我在经过大量迭代和偏离路线的谷歌搜索后所做的。
getUserProjectCount: function (req, res) {
Project.findAll(
{
attributes: ['User.username', [sequelize.fn('COUNT', sequelize.col('Project.id')), 'ProjectCount']],
include: [
{
model: User,
attributes: [],
include: []
}
],
group: ['User.username'],
raw:true
}
).then(function (projects) {
res.send(projects);
});
}
where my reference models are
我的参考模型在哪里
//user
var User = sequelize.define("User", {
username: Sequelize.STRING,
password: Sequelize.STRING
});
//project
var Project = sequelize.define("Project", {
name: Sequelize.STRING,
UserId:{
type:Sequelize.INTEGER,
references: {
model: User,
key: "id"
}
}
});
Project.belongsTo(User);
User.hasMany(Project);
after migration ORM create 'Users' & 'Projects' table into my postgres server. Here is SQL Query by ORM
迁移 ORM 后，将“用户”和“项目”表创建到我的 postgres 服务器中。这是 ORM 的 SQL 查询
SELECT
"User"."username", COUNT("Project"."id") AS "ProjectCount"
FROM
"Projects" AS "Project"
LEFT OUTER JOIN "Users" AS "User" ON "Project"."UserId" = "User"."id"
GROUP BY
"User"."username";
