The following SQLite code groups Messages by conversation_id:
@messages=Message.where("messages.sender_id = (?) OR messages.recipient_id = (?)"
, current_user.id, current_user.id).group("messages.conversation_id")
In moving over to Heroku, this code isn't recognized by Postgres. Looking at the logs, I'm told to add all Message columns to GROUP BY - getting me to this functional code:
@messages=Message.where("messages.sender_id = (?) OR messages.recipient_id = (?)"
, current_user.id, current_user.id).group("messages.conversation_id
, messages.updated_at, messages.id, messages.sender_id
, messages.recipient_id, messages.sender_deleted
, messages.recipient_deleted, messages.body, messages.read_at
, messages.ancestry, messages.ancestry_depth, messages.created_at")
Only this code doesn't group by conversation_id correctly. It simply outputs all messages that meet the WHERE condition. Any ideas?
I arrived at a functional solution with the use of DISTINCT ON:
@messages = Message.select("DISTINCT ON (messages.conversation_id) * ")
.where("messages.sender_id = (?) OR messages.recipient_id = (?)", current_user.id, current_user.id)
.group("messages.conversation_id, messages.updated_at, messages.id, messages.sender_id, messages.recipient_id, messages.sender_deleted, messages.recipient_deleted, messages.body, messages.read_at, messages.ancestry, messages.ancestry_depth, messages.created_at")
However, this wont work in SQLite. Downloading Postgres and using it directly rather than having to use SQLite code in development and Postgres code in production (Heroku) is recommended.
PostgreSQL requires that you group all non-aggregate attributes. Grouping is kind of saying you want the unique combinations of the grouped attributes, so asking all the information does not make sense. I don't know about RoR, but you'll have to ask for the conversation_id alone (with possible aggregate information if you need it). But since you're asking for all information on the messages table, I'm thinking you might actually want to order by the conversation ID?
