TaggableModel.tagged_with doesn't work with PostgreSQL #6

Closed
dslh opened this Issue Mar 16, 2012 · 2 comments

1 participant

@dslh

The problem is that when you use an aggregate function in a query, postgres strictly requires that all other columns should be listed in the GROUP BY clause. So, supposing my model is User, with an id, name and attr_taggable :roles, and I attempt to execute User.tagged_with ['admin'], the resulting query will be:

SELECT * FROM (
  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')
    GROUP BY users.id
) users

Postgres will complain:

ERROR: column "users.name" must appear in the GROUP BY clause or be used in an aggregate function

This is a problem for me because Heroku is backed by postgres. A synonymous query that postgres accepts is:

SELECT * FROM users WHERE id IN (
  SELECT taggable_id
    FROM taggings INNER JOIN tags
      ON tags.id = taggings.tag_id
    WHERE tags.name IN ('admin')
      AND taggings.taggable_type = 'User'
)

Or, to get the functionality of the :all => true option:

SELECT * FROM users WHERE 2 = (
  SELECT count(*)
    FROM taggings INNER JOIN tags
      ON tags.id = taggings.tag_id
    WHERE tags.name IN ('admin','sales')
      AND taggings.taggable_id=users.id
      AND taggings.taggable_type = 'User'
)

And of course a similar query can be used for :min => n. These queries also work with SQLite3, I'm not sure about other db engines but I think it's all standard SQL syntax.

I don't really know how to convert this into query builder syntax but I will give it a shot when I get time.

@dslh

Ok, I see that tags_count is a part of the spec. A revised query:

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

The :min, :all and :on options are easily implemented with minor additions. I'm getting the hang of squeel but I'm not sure how you would coax this sort of nested statement out of it. Any thoughts?

@dslh

Upgrading to PostgreSQL 9.1 resolves this issue.

@dslh dslh closed this Mar 22, 2012
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment