rails query join, order by, group by issue

Question

Have three models in project

• conversation
• conversation_message
• message

Conversation.rb

class Conversation < ActiveRecord::Base
    #subject
    has_many :conversation_messages, :dependent => :destroy
    has_many :messages, :through => :conversation_messages, :dependent => :destroy, :order => "created_at DESC"
end

ConversationMessage.rb

class ConversationMessage < ActiveRecord::Base
  #conversation_id, message_id
  belongs_to :conversation
  belongs_to :message, :dependent => :destroy
end

Message.rb

#sender_id, receiver_id, message

has_one :conversation_message, :dependent => :destroy
has_one :real_conversation, :through => :conversation_message, :source => "conversation"

So I want to retrieve current_user's all conversations and want to show them in order of last message received or sent. Also want to show single conversation for all of its messages and conversation must ordered by messages included in it. Using rails 3.0

I tried following query but gives me an error below query

@user_conversations = Conversation
.joins(:messages)
.where(["messages.receiver_id = ?  or messages.sender_id = ?", current_user.id, current_user.id ])
.group("conversations.id").order("messages.created_at DESC")

error

PG::GroupingError: ERROR:  column "messages.created_at" must appear in the GROUP BY clause or be used in an aggregate function

Answer 1

You'll need to include messages.created_at in the group clause:

@user_conversations = Conversation
.joins(:messages)
.where(["messages.receiver_id = ?  or messages.sender_id = ?", current_user.id, current_user.id ])
.group("conversations.id, messages.created_at")
.order("messages.created_at DESC")

See: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function

Answer 2

You can avoid showing multiple conversations by using

.order("max(messages.created_at) DESC")

and removing messages.created_at from group section.

@user_conversations = Conversation
.joins(:messages)
.where(["messages.receiver_id = ?  or messages.sender_id = ?", current_user.id, current_user.id ])
.group("conversations.id")
.order("max(messages.created_at) DESC")

It gives you "unique conversations ordered by latest comments".

It's not purely ordering by messages.created_at but with changing max and min and DESC and ASC will do the job you want, I guess.

Added: It's been many years since I wrote this answer, and I found out that this does not return the records if there is no messages.
In order to handle that you need to add NULLS LAST or NULLS FIRST in the query (for postgreql, I don't know for mysql).
ex. .order("max(messages.created_at) DESC NULLS LAST")