Nested query in squeel

Question

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

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}

Which generates this query:

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 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

Is there any way to express this using squeel?

What is you PostgreSQL version? – Erwin Brandstetter Mar 19 '12 at 07:22 — Erwin Brandstetter, Mar 19 '12 at 07:22

Erwin Brandstetter · Accepted Answer · 2012-03-19T07:33:16.910

2

I wouldn't know about Squeel, but the error you see could be fixed by upgrading PostgreSQL.

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

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:

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause

BTW, your alternative query can be simplified, an additional DISTINCT would be redundant.

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

edited Mar 19 '12 at 07:33

answered Mar 19 '12 at 07:26

Erwin Brandstetter

605,456
145
1,078
1,228

That's really good to know, thanks! Unfortunately I'm running the application on heroku, so an upgrade from 8.3 to 9.1 will cost me at least $200 a month, but if the problem is specific to old versions of postgres then I guess I will accept that I have to work around with find_by_sql. I'm still curious though if squeel can be used for this sort of query. – dslh Mar 19 '12 at 10:20
@dslh: [Heroku Labs already offers 9.1](http://devcenter.heroku.com/articles/labs-heroku-shared-postgresql). You may be interested. – Erwin Brandstetter Mar 19 '12 at 12:46
That doesn't answer my question but it did solve my problem. I guess I was asking the wrong question. Thanks very much! – dslh Mar 22 '12 at 20:02

Nested query in squeel

1 Answers1

Linked