CODEWITHSUNDEEP
ruby-on-railspostgresqlherokusqlite
slindsey3000
slindsey3000

Reputation: 4271

Transform query that works in SQLite into something that works in Postgres

Message.order("created_at DESC").where(user_id: current_user.id).group(:sender_id, :receiver_id).count

Works with my dev environment SQLite3 for a Rails 3.2 app but fails when pushed to Heroku using Postgres with this error: 

PG::GroupingError: ERROR: column "messages.created_at" must appear in the GROUP BY clause or be used in an aggregate function

It seems it wants :created_at in the query. Unable to come up with anything. Any ideas?

Thanks

Upvotes: 2

Views: 69

Answers (1)

Marth
Marth

Reputation: 24802

PostgreSQL can't figure out how to order by your created_at.

Suppose that you find 2 groups of (:sender_id, :receiver_id), for instance [1, 2] and
[1, 3].
Now suppose that in the first group you have 2 messages, one from 1 day ago and one from 1 minute ago. And let's say you have 1 message in the second group from 12 hours ago.
Then ORDER BY created_at DESC doesn't make any sense: do you take the message from 1 day ago as the created_at of the first group (hence the first group appears after the second one), or the one from 1 minute ago (in which case the first group now appears first)?

That's why PostgreSQL says that you need to either have created_at in the GROUP BY (in which case you now have 3 different group, as the first one is now split in two), or you need to use an aggregate function to transform multiple values of created_at into a single one.

This will run (I don't know what you expect the results to be, you might not want to use MAX(created_at) ! You can find a list of PostgreSQL's aggregate functions here) :

Message.order("MAX(created_at) DESC")
       .where(user_id: current_user.id)
       .group(:sender_id, :receiver_id)
       .count

Upvotes: 1

Related Questions