编写Rails Scope：筛选客人与会员双向互发消息的Booking记录

我们可以通过两个EXISTS子查询直接在Booking模型中实现所需的scope，逻辑集中且高效，无需拆分到其他模型。

实现Booking的目标scope #

class Booking < ApplicationRecord
  belongs_to :guest
  belongs_to :member

  scope :with_mutual_messages, -> {
    # 条件1：当前预订的客人，向对应会员发送过消息
    joins(:guest, :member)
      .where(
        Guest.where("id = bookings.guest_id").exists?(
          conversation_items: {
            conversation_id: ConversationParticipation.where(
              "participant_type = 'Member' AND participant_id = bookings.member_id"
            ).select(:conversation_id),
            author_type: 'Guest',
            author_id: Guest.arel_table[:id]
          }
        )
      )
      # 条件2：当前预订的会员，向对应客人发送过消息
      .where(
        Member.where("id = bookings.member_id").exists?(
          conversation_items: {
            conversation_id: ConversationParticipation.where(
              "participant_type = 'Guest' AND participant_id = bookings.guest_id"
            ).select(:conversation_id),
            author_type: 'Member',
            author_id: Member.arel_table[:id]
          }
        )
      )
  }
end

方案逻辑说明 #

• 第一个WHERE EXISTS子查询：验证当前预订的客人，是否在包含对应会员的对话中发送过消息。
• 第二个WHERE EXISTS子查询：验证当前预订的会员，是否在包含对应客人的对话中发送过消息。
• 两个条件同时满足时，筛选出互相发送过消息的预订记录，EXISTS子查询不会产生重复数据，检索效率更高。

可选：拆分到Member/Guest模型的scope（若你偏好拆分逻辑） #

如果你希望将消息验证逻辑拆分到对应模型，可参考以下修正后的scope：

Member模型的scope

class Member < ApplicationRecord
  # ...
  scope :has_sent_message_to_guest, ->(guest) {
    exists?(
      conversation_items: {
        conversation_id: guest.conversations.select(:id),
        author_type: 'Member',
        author_id: arel_table[:id]
      }
    )
  }
end

Guest模型的scope

class Guest < ApplicationRecord
  # ...
  scope :has_sent_message_to_member, ->(member) {
    exists?(
      conversation_items: {
        conversation_id: member.conversations.select(:id),
        author_type: 'Guest',
        author_id: arel_table[:id]
      }
    )
  }
end

基于拆分scope的Booking实现

class Booking < ApplicationRecord
  # ...
  scope :with_mutual_messages, -> {
    joins(:guest, :member)
      .where(Guest.has_sent_message_to_member(Member.arel_table[:id]))
      .where(Member.has_sent_message_to_guest(Guest.arel_table[:id]))
  }
end

内容的提问来源于stack exchange，提问作者user2475306