ruby on rails - ActiveRecord Group by 2 columns

Question

Message模型

user_id      # owner of the message 
sender_id    # user that sent message
receiver_id  # user then recieved message 
content      # content of message

有Messages。我想把它们按sender_id和receiver_id分组，因为它们是一个"对话线程"。

当我执行group时，我得到的结果看起来像

[1,3] => 5   # user 1 and 3 have 5 messages
[1,6] => 2   # user 1 and 6 have 2 messages
[3,1] => 3   # user 3 and 1 have 3 messages

真的[1,3]和[3,1]是同一个"组"的一部分。我怎样才能做到这一点呢?

Answer 1

你可以通过使用LEAST()和GREATEST()来排序你的sender_id和recipient_id(至少在Postgres和MySQL中)。

下面是演示SQL:

SELECT 
  LEAST(sender_id, receiver_id),
  GREATEST(sender_id, receiver_id),
  count(*)
FROM messages
GROUP BY
  LEAST(sender_id, receiver_id),
  GREATEST(sender_id, receiver_id)

和对应的ActiveRecord:

Message.group('LEAST(sender_id, receiver_id), GREATEST(sender_id, receiver_id)').count

Answer 2

使用@Kristján提供的技巧，您可以在ruby中合并[1,3]和[1,3]，而不是使用SQL函数:

thread_messages_count = Hash.new(0)
Message.group(:sender_id, :receiver_id).count.each do |k, v|
  thread_messages_count[k.sort] += v
end