SQLite to Postgres (Heroku) GROUP BY

Question

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?

Answer 1

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.