sql query with group by works on SQLite and not on postgresql

5
votes

The following query works on local but not on production: (Production is heroku is running postgreSQL and locally I am running a sqllite database)

Ruby

Tutor.joins(:expertises).where(:expertises => {:subject_id => [2,4]}).group("tutors.id").having("COUNT(*) = 2")

SQL

SELECT "tutors".* FROM "tutors" INNER JOIN "expertises" ON "expertises"."tutor_id" = "tutors"."id" WHERE ("expertises"."subject_id" IN (9)) GROUP BY tutors.id HAVING COUNT(*) = 1 ORDER BY rank DESC)

I get the following error on production ActiveRecord::StatementInvalid (PGError: ERROR: column "tutors.fname" must appear in the GROUP BY clause or be used in an aggregate function

I have the following values in my table

id                 :integer         not null, primary key
fname              :string(255)
lname              :string(255)
school             :string(255)
major              :string(255)
year               :string(255)
bio                :text
vid                :string(255)
price              :integer
phone              :string(255)
skype              :string(255)
address            :text

When I try to adjust the query to group by all attributes, I get another error:

Ruby

>> Tutor.joins(:expertises).where(:expertises => {:subject_id => [2,4]}).group("tutors.*").having("COUNT(*) = 2")

SQL

SELECT "tutors".* FROM "tutors" INNER JOIN "expertises" ON "expertises"."tutor_id" =    "tutors"."id" WHERE ("expertises"."subject_id" IN (2, 4)) GROUP BY tutors.* HAVING COUNT(*)    = 2 ORDER BY rank DESC

ActiveRecord::StatementInvalid: PGError: ERROR: could not identify an ordering operator for type tutors HINT: Use an explicit ordering operator or modify the query.

Help!

3
ruby-on-railssqlitepostgresql

3 Answers

5
votes

PostgreSQL does not support unaggregated and ungrouped expressions in GROUP BY queries.

Use this:

SELECT  t.*
FROM    (
        SELECT  tutor_id
        FROM    expertises e
        WHERE   e.subject_id IN (2, 4)
        GROUP BY
                tutor_id
        HAVING  COUNT(*) = 2
        ) e
JOIN    tutor t
ON      t.id = e.tutor_id

This is cross-platform.

3
votes

Hmm, nice try, but asterisk expansion doesn't work that way in the GROUP BY clause. You need to actually list all the columns. Or upgrade to PostgreSQL 9.1 in a few months.

2
votes

Try this:

subjects      = [1,2]
sub_query_sql = Tutor.select("tutors.id").joins(:expertises).
  where(:expertises => {:subject_id => subjects}).
  group("tutors.id").having("COUNT(*) = #{subjects.size}").to_sql

Tutor.joins("JOIN (#{sub_query_sql}) A ON A.id = tutors.id")