基于给定Rails表结构查询用户未读消息及对应未静音事件咨询

背景说明 #

组织方（event.user）也需要存在attendance记录（可标记muted为true或false）。

数据库模型（简化自db/schema.rb） #

create_table "attendances" do |t|
  t.bigint "user_id"
  t.bigint "event_id"
  t.boolean "muted"
end

create_table "events" do
# 省略原有非关联字段
end

create_table "message_reads" do |t|
  t.bigint "message_id"
  t.bigint "user_id"
end

create_table "messages" do |t|
  t.string "from_type"
  t.bigint "from_id"
  t.string "to_type"
  t.bigint "to_id"
  t.text "content"
end

create_table "users" do |t|
  t.string "email"
end

需求规则说明 #

• 需为指定用户查询两类数据：
  1. 该用户的未读消息列表
  2. 存在未读消息的、该用户参与的事件列表
• 优先用带GROUP BY的同一条查询语句实现
• 消息发送方为用户本人时，默认标记为已读，不计入统计
• 仅统计该用户参与、且对应attendance记录中muted = false的事件关联消息

具体实现 #

方案1：单SQL查询同时返回两类结果（推荐） #

假设消息关联事件时to_type字段值为Event，可根据实际多态命名规则调整：

-- 替换:current_user_id为指定查询的用户ID
SELECT 
  m.*,
  e.id AS event_id,
  COUNT(m.id) OVER (PARTITION BY e.id) AS event_unread_total
FROM messages m
INNER JOIN events e ON m.to_type = 'Event' AND m.to_id = e.id
INNER JOIN attendances a ON a.event_id = e.id AND a.user_id = :current_user_id AND a.muted = FALSE
LEFT JOIN message_reads mr ON mr.message_id = m.id AND mr.user_id = :current_user_id
WHERE
  NOT (m.from_type = 'User' AND m.from_id = :current_user_id)
  AND mr.id IS NULL
GROUP BY e.id, m.id
ORDER BY e.id, m.created_at DESC;

结果使用说明：

• 每行对应一条未读消息，附带所属事件ID、该事件下的未读消息总数
• 对结果中的event_id字段去重，即可直接得到「存在未读消息的用户参与事件列表」

方案2：Rails ActiveRecord 适配实现 #

# current_user为指定查询的用户实例
unread_messages = Message.joins("INNER JOIN attendances ON attendances.event_id = messages.to_id AND messages.to_type = 'Event'")
  .where(attendances: { user_id: current_user.id, muted: false })
  .where.not(from_type: 'User', from_id: current_user.id)
  .left_joins(:message_reads)
  .where(message_reads: { id: nil })
  .select('messages.*, attendances.event_id, COUNT(messages.id) OVER (PARTITION BY attendances.event_id) as event_unread_total')
  .group('attendances.event_id, messages.id')
  .order('attendances.event_id, messages.created_at DESC')

# 提取存在未读消息的事件列表
events_with_unread = Event.where(id: unread_messages.pluck(:event_id).uniq)

可选优化 #

如果不需要每条消息附带所属事件的未读数，可拆分两次查询，性能开销更低：

• 第一次单独查询未读消息列表
• 第二次用未读消息关联的事件ID去重后查询事件列表

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