CODEWITHSUNDEEP
ruby-on-railsactiverecord
kittyminky
kittyminky

Reputation: 485

Active Record Rails 4 Unique Query on Collection

My users have many 'received_messages' and 'sent_messages' (from a join table that just contains a receiver_id, sender_id, and the content). My associations are all set up correctly and when I call user.received_messages and user.sent_messages I am returned the correct collections. What I am now trying to do is filter the returned message collections so they will only return the first(newest) message from each unique sender(if it is received_messages) or receiver(if it is sent_messages).

I have tried :

@sent_messages = @user.sent_messages.uniq(&:receiver_id) @sent_messages = @user.sent_messages.select(:receiver_id).distinct Etc but this keeps returning to me an array of numbers rather than actual collections.

Not sure if this is relevant but in my User model the associations are set up as :

has_many :received_messages, class_name: 'Message', foreign_key: 'receiver_id', table_name: 'messages' has_many :sent_messages, class_name: 'Message', foreign_key: 'sender_id', table_name: 'messages'

and in the Message model:

belongs_to :sender, foreign_key: 'sender_id', class_name: "User" belongs_to :receiver, foreign_key: 'receiver_id', class_name: "User"

Upvotes: 1

Views: 398

Answers (3)

MrYoshiji
MrYoshiji

Reputation: 54882

Try this:

message_per_uniq_receiver = @user.sent_messages.select('DISTINCT ON (receiver_id) *')
                                               .order('receiver_id, messages.created_at DESC')

message_per_uniq_sender = @user.received_messages.select('DISTINCT ON (sender_id) *')
                                                 .order('sender_id, messages.created_at DESC')

This DISTINCT ON is kinda complex ( documentation here ):

Long story short, the DISTINCT ON(sender_id) will create groups for each distinct sender_id found in the messages table, and will select the first of each group, the first of the group is determined by the ORDER BY clause.

Upvotes: 2

jkdev
jkdev

Reputation: 11738

Start with your list of distinct receiver ID's. Call it receiver_id_list. Then iterate through them. For each ID, find the newest message belonging to that receiver, and add it to an array. 

most_recent_sent_messages = []

receiver_id_list.each do |id|
  most_recent_sent_messages << @user.sent_messages.where(receiver_id: id).order(time_sent: :desc).first
end

The highest time_sent (or whatever you named the timestamp) should be the newest message, which is why you should sort them in descending order.

Upvotes: 0

jkdev
jkdev

Reputation: 11738

The "select" method is like the SELECT command in SQL. It selects a database column and returns data.

Thus, sent_messages.select(:receiver_id) returns the receiver ID, which is an integer.

If you want sent_messages with a particular receiver ID, use the "where" method.

For example, sent_messages.where(receiver_id: some_receiver_id).

Upvotes: 0

Related Questions