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

Question

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

Did you try adding :created_at to the query, per its desire? Message.order("created_at DESC").where(user_id: current_user.id).group(:sender_id, :receiver_id, :created_at).count — hd1
– hd1, Commented May 24, 2015 at 23:11
@hd1: While this will make Postgre accept the query, the results will probably not be the desired ones: 2 messages from user 1 to user 2 won't be grouped together unless they were sent at the exact time, which is rather unlikely (and probably not the wanted behaviour). — Marth
– Marth, Commented May 25, 2015 at 9:09
Yes to both comments above. Adding :created_at will make the query work for Postgres but the result is not correct. In effect it does not group anymore as every message has a unique :created_at column. — slindsey3000
– slindsey3000, Commented May 25, 2015 at 15:54
@slindsey3000: Did the answer I posted below not work for you? — Marth
– Marth, Commented May 25, 2015 at 17:09
I am waiting for traffic on my site to die down a little bit sir then will push it up to production. I have to have a Rails.env flag because I run SQLite local. Will advise. Thank you sir. Hope it works! — slindsey3000
– slindsey3000, Commented May 25, 2015 at 17:10

Marth · Accepted Answer · 2015-05-24 23:42:35Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related