SQLite for Postgres (Heroku) GROUP BY

Question

The following groups of SQLite code messages are called by a dialog box:

@messages=Message.where("messages.sender_id = (?) OR messages.recipient_id = (?)" , current_user.id, current_user.id).group("messages.conversation_id") 

When switching to Heroku, this code is not recognized by Postgres. Looking at the logs, I was told to add all the message columns to GROUP BY - bringing 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 is not grouped in different ways. It simply displays all messages that satisfy the WHERE clause. Any ideas?

Answer 1

I came up with a functional solution using 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 will not work in SQLite. It is recommended that you download Postgres and use it directly, rather than using SQLite code in development and Postgres code in production (Heroku).

Answer 2

PostgreSQL requires you to group all non-aggregate attributes. Grouping means that you need unique combinations of grouped attributes, so it makes no sense to request all the information. I do not know about RoR, but you only need to ask for conversation_id (with possible aggregate information if you need it). But since you are requesting all the information in the message table, I think you can really order by conversation ID?