问题描述
简短版本:如何使用squeel编写此查询?
Short version: How do I write this query in squeel?
SELECT OneTable.*, my_count
FROM OneTable JOIN (
SELECT DISTINCT one_id, count(*) AS my_count
FROM AnotherTable
GROUP BY one_id
) counts
ON OneTable.id=counts.one_id
长版:是为模型添加简单标记的瑰宝。它添加了方法 tagged_with
。假设我的模型是 User
,具有ID和名称,我可以调用 User.tagged_with ['admin','sales']
。在内部,它使用以下代码:
Long version: rocket_tag is a gem that adds simple tagging to models. It adds a method tagged_with
. Supposing my model is User
, with an id and name, I could invoke User.tagged_with ['admin','sales']
. Internally it uses this squeel code:
select{count(~id).as(tags_count)}
.select("#{self.table_name}.*").
joins{tags}.
where{tags.name.in(my{tags_list})}.
group{~id}
生成以下查询的内容:
SELECT count(users.id) AS tags_count, users.*
FROM users INNER JOIN taggings
ON taggings.taggable_id = users.id
AND taggings.taggable_type = 'User'
INNER JOIN tags
ON tags.id = taggings.tag_id
WHERE tags.name IN ('admin','sales')
GROUP BY users.id
一些RDBMS对此感到满意,但postgres抱怨:
Some RDBMSs are happy with this, but postgres complains:
ERROR: column "users.name" must appear in the GROUP BY
clause or be used in an aggregate function
我相信写查询的一种更可接受的方式是:
I believe a more agreeable way to write the query would be:
SELECT users.*, tags_count FROM users INNER JOIN (
SELECT DISTINCT taggable_id, count(*) AS tags_count
FROM taggings INNER JOIN tags
ON tags.id = taggings.tag_id
WHERE tags.name IN ('admin','sales')
GROUP BY taggable_id
) tag_counts
ON users.id = tag_counts.taggable_id
是
推荐答案
我不知道Squeel,但是您看到的错误可以修复通过升级PostgreSQL。
I wouldn't know about Squeel, but the error you see could be fixed by upgrading PostgreSQL.
错误:列 users.name必须出现在GROUP BY子句中,或者必须在聚合函数中使用
ERROR: column "users.name" must appear in the GROUP BY clause or be used in an aggregate function
从PostgreSQL 9.1开始,一旦您在GROUP BY中列出了主键,就可以跳过该表的其他列,并仍在SELECT列表中使用它们。 告诉我们:
Starting with PostgreSQL 9.1, once you list a primary key in the GROUP BY you can skip additional columns for this table and still use them in the SELECT list. The release notes for version 9.1 tell us:
BTW,您的替代查询可以得到简化,另外需要 DISTINCT
将是多余的。
SELECT o.*, c.my_count
FROM onetable o
JOIN (
SELECT one_id, count(*) AS my_count
FROM anothertable
GROUP BY one_id
) c ON o.id = counts.one_id
这篇关于squeel中的嵌套查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!