Is this possible to do in one query?

Question

I have two models:

class User
end

class Message
 belongs_to :sender, :class_name=> 'User'
 belongs_to :recipient, :class_name=> 'User'
end

I want to fetch all the buddies of a given user ordered by most recent date of message what appears in conversation between given user and his buddy and, if it possible in same query, to fetch the number of message in their conversation.

Now, I'm stuck at this:

Messages.all(:joins => :sender,
   :conditions => ['sender_id = ? OR recipient_id = ?', some_user.id, some_user.id],
   :select => 'users.*, sender_id, recipient_id, MAX(messages.created_at) as last_created, COUNT(messages.id) as messages_count', 
   :group => 'messages.sender_id, messages.recipient_id',
   :order => 'last_created DESC'

That query produce this output:

a)

users.* | sender_id | recipient_id | MAX(last_created) | messages_count
user1   | 1         | 2            | bla               | bla
user1   | 1         | 3            | bla               | bla
user1   | 1         | 4            | bla               | bla

Because models joined by messages.sender_id = user.id I have only user1 records fetched but I need user2, user3 and user4 records in that special situation A when user1 has only send messages to his buddies.

b)

users.* | sender_id | recipient_id | MAX(last_created) | messages_count
user2   | 2         | 1            | bla               | bla
user3   | 3         | 1            | bla               | bla
user4   | 4         | 1            | bla               | bla

In situation B, otherwise, i have what i want to have - all three buddies ordered by most recent date of message what appears in conversation between given user and his buddy.

c)

users.* | sender_id | recipient_id | MAX(last_created) | messages_count
user1   | 1         | 2            | bla               | bla
user3   | 3         | 1            | bla               | bla
user4   | 4         | 1            | bla               | bla

Situation C. user2 as buddy of user1 is missing cause :joins => :sender. Otherwise, if :joins => :recipient would be missing user3 and user4. Thats the cracker. It's no matter how we join models. How to solve this situation in one query?

Answer 1

Looks like you want an alternative "joins" string (ie not :joins => :sender). Would :joins => :recipient give the correct response for situation B?

If not - you can also pass in hand-crafted SQL to the :joins key and join the tables however you like.

Looks like there's a good tutorial covering joins here: http://www.railway.at/articles/2008/04/24/database-agnostic-database-ignorant/

Answer 2

You need the select_extra_columns gem to return join/aggregate columns. Assuming you have installed the gem, modify your User model as shown below.

class User
  select_extra_columns


  def friends_with_conversation
    User.all(
     :select => "users.*, b.last_message_at, b.message_count",
     :joins  => "
            RIGHT JOIN
             ( SELECT   IF(a.sender_id=#{self.id}, a.recipient_id, 
                             a.sender_id) AS friend_id, 
                        MAX(a.created_at) AS last_message_at, 
                        COUNT(a.id)       AS message_count
               FROM     messages AS a
               WHERE    a.sender_id = #{self.id} OR 
                        a.recipient_id = #{self.id}
               GROUP BY IF(a.sender_id=#{self.id}, a.recipient_id, 
                             a.sender_id)
             ) AS b ON users.id = b.friend_id
           ", 
      :order  => "b.last_message_at DESC",      
      :extra_columns => {:last_message_at=>:datetime, :message_count => :integer}
    )
  end  
end

Now you can make following calls to get the friend details.

user.friends_with_conversation.each do |friend|
  p friend.name
  p friend.last_message_at
  p friend.message_count
end

You need the gem to return last_message_at and message_count in the User object returned by the query.

Edit I am not familiar with PostgresSQL. Cursory reading of the documentation suggests, following SQL might work.

:joins  => "
 RIGHT JOIN
 ( SELECT   CASE WHEN a.sender_id=#{self.id} 
                 THEN a.recipient_id 
                 ELSE a.sender_id 
            END               AS friend_id, 
            MAX(a.created_at) AS last_message_at, 
            COUNT(a.id)       AS message_count
   FROM     messages AS a
   WHERE    a.sender_id = #{self.id} OR 
            a.recipient_id = #{self.id}
   GROUP BY CASE WHEN a.sender_id=#{self.id} 
                 THEN a.recipient_id 
                 ELSE a.sender_id 
            END
 ) AS b ON users.id = b.friend_id
"