Ruby-on-rails rails query join, order by, group by issue

rails query join, order by, group by issue

提问by chaitanya

Have three models in project

项目中有三个模型

• conversation
• conversation_message
• message

• 对话
• 对话消息
• 信息

Conversation.rb

对话.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

会话消息.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

所以我想检索current_user's 所有对话，并希望按照收到或发送的最后一条消息的顺序显示它们。还希望为其所有消息显示单个对话，并且对话必须按其中包含的消息排序。使用导轨 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

回答by CDub

You'll need to include messages.created_atin the groupclause:

您需要messages.created_at在group条款中包括：

@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

请参阅：列“users.id”必须出现在 GROUP BY 子句中或用于聚合函数中

回答by foloinfo

You can avoid showing multiple conversations by using

您可以通过使用避免显示多个对话

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

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

and removing messages.created_atfrom groupsection.

并messages.created_at从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 ordered by latest comments".

它为您提供“按最新评论排序的独特对话”。

It's not purely ordering by messages.created_atbut with changing maxand minand DESCand ASCwill do the job you want, I guess.

它不是纯粹的订货messages.created_at，但不断变化max和min和DESC和ASC会做你想要的工作，我猜。