Connection rails requests, order, release group

Question

You have three models in the 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 get current_user all the conversations and want to show them in the order they received or sent the last message. You also want to show a single conversation for all your messages, and the conversation must be ordered by the messages included in it. Using rails 3.0

I tried the following query but gave me an error below the 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") 

Mistake

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

Answer 1

You 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" should appear in a GROUP BY clause or used in an aggregate function

Answer 2

You can avoid displaying multiple conversations with

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

and removing the messages.created_at section from group .

 @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 sorted by latest comments."

This is not purely ordering on messages.created_at , but with changes to max and min and DESC and ASC , I think it will complete the task you want.