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

Question

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!

Quassnoi · Accepted Answer · 2011-05-14 23:31:15Z

5

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.

answered May 14, 2011 at 23:31

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter Eisentraut · Accepted Answer · 2011-05-14 23:29:55Z

3

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.

answered May 14, 2011 at 23:29

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

1 Comment

user531065 Over a year ago

OK thank you. I listed them and it works but this type of query is invalid on sqllite. I ended up just having 2 queries (one where every column is listed for postgre and one where only the first is listed for sqllite) and keying off of an environment variable to determine which one I do. Thanks for your help!

Harish Shetty · Accepted Answer · 2011-05-15 01:52:38Z

2

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

answered May 15, 2011 at 1:52

Harish Shetty

64.4k21 gold badges154 silver badges201 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related