包括选择，排序，多个模型的限制（单个查询） [英] Include, Select, Sort, Limit from multiple models (single query)

问题描述

我需要创建一个查询，其中包括从下表数据：

*对话：一种模式，用户之间的群组的邮件

 类会话与LT;的ActiveRecord :: Base的
  ＃协会
  的has_many：消息，取决于：摧毁
  的has_many：conversation_participants
  的has_many：用户：通过=＆GT; ：conversation_participants
  ##属性称号，created_at，的updated_at
结束
 

---

* ConversationParticipant ：一个模型，跟踪会话的用户

 类ConversationParticipant＆LT;的ActiveRecord :: Base的
  ##协会
  belongs_to的：对话
  belongs_to的：用户
  ##属性conversation_id，USER_ID，可见，created_at，的updated_at
结束
 

---

*消息：一个模型，跟踪内容和发件人

 类信息＆LT;的ActiveRecord :: Base的
  belongs_to的：对话
  belongs_to的：发件人：=将class_name＆GT; 用户
  ##属性sender_id，内容，conversation_id，created_at，的updated_at
结束
 

---

*用户：与属性名称

模型

---

如何在单个查询以下？

  

  
1. 限制（5）的的消息最近的消息 的的uniq中的的会话的
  
2. 其中 USER_ID = current_user.id从 ConversationParticipant 的
  
3. 为了 见过 = 假，然后的updated_at降序从 ConversationParticipant 的
  
4. 包括 会话的
  
5. 包括（ 信息的发件人）=> 用户的
  
6. 包括从其他参与者的的 ConversationParticipant 的 => 用户
  

---

注：的包括的和的选择的是很重要的，因为这个问题是为了减少查询的次数

解决方案

这里是我包括了所有需要的车型，该查询被转换为5的SQL查询，因为preLOAD不加入（运行在一个单独的查询）。

  Message.joins（LEFT JOIN消息作为M于messages.id！= m.id
              与m.conversation_id = messages.conversation_id
              与messages.created_at＆LT; m.created_at）
       。凡（m.id IS NULL）
       .joins（INNER JOIN conversation_participants AS CP
              ON cp.conversation_id = messages.conversation_id
              与cp.user_id =＃{USER_ID}）
       .order（cp.seen，cp.updated_at降序）
       .limit（5）
       .includes（：发送方）
       .includes（对话：[{conversation_participants：用户}]）
 

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.

解决方案

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}])

这篇关于包括选择，排序，多个模型的限制（单个查询）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！