Can this be done in one request?

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 get all the friends of a certain user, sorted by the latest date of the message that appears in the conversation between this user and his friend , and, if possible in the same request, the number of messages in their conversation.

Now I'm stuck on 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'

This query produces this output:

and)

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

Since the models connected using messages.sender_id = user.id, I only have user1 entries, but I need user2, user3 and user4 entries in this special situation A, when user1 only sends messages to his friends.

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

B, , , - , .

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

C. user2 user1 :joins => :sender. , :joins => :recipient user3 user4. . , . ?

Answer 1

join/aggregate select_extra_columns gem. , , , .

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

, .

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

, last_message_at message_count User, .

Edit PostgresSQL. , SQL .

: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
"

Answer 2

, "" (.. :joins => :sender). :joins => :recipient B?

- SQL- :joins , .

, , : http://www.railway.at/articles/2008/04/24/database-agnostic-database-ignorant/