Include, Select, Sort, Limit from multiple models

问题:

I need to create a single query that includes data from the following tables:

*Conversation: a model that groups messages between users

class Conversation < ActiveRecord::Base
  # Associations
  has_many :messages, dependent: :destroy
  has_many :conversation_participants
  has_many :users, :through => :conversation_participants
  ## Attributes title, created_at, updated_at
end

---

* ConversationParticipant: a model that keeps track of the users of the conversation

class ConversationParticipant < ActiveRecord::Base
  ## Associations
  belongs_to :conversation
  belongs_to :user
  ## Attributes conversation_id, user_id, seen, created_at, updated_at
end

---

* Message: a model that keeps track content and sender

class Message < ActiveRecord::Base
  belongs_to :conversation
  belongs_to :sender, :class_name => "User"
  ## Attributes sender_id, content, conversation_id, created_at, updated_at
end

---

*User: a model with attribute name

---

How to get the following in a single query?

1. limit of (5) recent messages from Message of uniq Conversation
2. where user_id = current_user.id from ConversationParticipant
3. order seen = false, then updated_at DESC from ConversationParticipant
4. includes Conversation
5. includes (Message sender) => User
6. includes the other participant from ConversationParticipant => User

---

Note: includes and select are important, as this question is meant to reduce the number of queries.

回答1:

Here is how I included all the needed models, this query is translated to 5 sql queries since preload doesn't join (runs in a separate query).

Message.joins("LEFT JOIN messages AS m ON messages.id != m.id 
              AND m.conversation_id = messages.conversation_id 
              AND messages.created_at < m.created_at")
       .where('m.id IS NULL')
       .joins("INNER JOIN conversation_participants AS cp 
              ON cp.conversation_id = messages.conversation_id 
              AND cp.user_id = #{user_id}")
       .order("cp.seen, cp.updated_at DESC")
       .limit(5)
       .includes(:sender)
       .includes(conversation: [{conversation_participants: :user}])