current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
4

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
up vote 0 down vote

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?

share|improve this answer
    
no, ordering by conversation_id only sorts them in a different way. what i am trying to show the last message within each conversation (which is a group of messages with the same conversation_id) –  neon Jun 3 '11 at 21:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.