Nested request in squeel
Short version: how to 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
Long version: rocket_tag is a gem that adds simple tagging to models. He adds a method tagged_with
. Suppose my model User
, with ID and name, I can call 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}
Which generates this request:
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
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 that a nicer way of writing the request would be the following:
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
Is there a way to express this with squeel?
source to share
I would not know about Squeel, but the error you are seeing could be fixed by updating PostgreSQL.
Some RDBMSs are happy with this, but postgres complains:
ERROR: column "users.name" must appear in GROUP BY clause or be used in aggregate function
As of PostgreSQL 9.1, once you list the primary key in the GROUP BY, you can skip the additional columns for that table and still use them in the SELECT list. the release notes for version 9.1 tell us:
Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause
By the way, your alternative request can be simplified, an additional one DISTINCT
will be superfluous.
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
source to share