CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecord
slindsey3000
slindsey3000

Reputation: 4301

ActiveRecord Group by 2 columns

Message model

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

I have Messages. I would like to group them by sender_id and receiver_id as these are a "conversation thread".

When I do group I get results that look like

[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

Really [1,3] and [3,1] are part of the same 'group'. How can I achieve this?

Upvotes: 2

Views: 1376

Answers (2)

Baldrick
Baldrick

Reputation: 24350

Using the trick provided by @Kristján, you can merge [1,3] and [1,3] in ruby instead of using SQL functions:

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

Upvotes: 2

Kristján
Kristján

Reputation: 18843

You can get this by sorting your sender_id and recipient_id using LEAST() and GREATEST() (at least in Postgres and MySQL).

Here's a fiddle demonstrating the 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)

And the ActiveRecord equivalent:

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

Upvotes: 3

Related Questions