Current conversations logic for simple messaging system

I am building a simple messaging system with Rails3 that allows users to send private messages to each other. Each set of users can have a single stream of messages between each other (like texting)

I am confused however on the logic that is used to build the view that shows all current conv开发者_开发百科ersations going on for a user. This includes conversations that might only include sent messages from myself.

My schema has the following:

  create_table "messages", :force => true do |t|
    t.integer  "from_id"
    t.integer  "to_id"
    t.string   "message"
    t.datetime "created_at"
    t.datetime "updated_at"
  end 

I would like to emulate something similar to FB messaging that shows a row for each conversation.`

Any ideas would be super helpful.

Thanks! Danny

There are two sets of messages to consider:

• Those where from_id is the current user.
• Those where to_id is the current user.

I'd go with a find_by_sql and let the database do all the work:

chatting_with = User.find_by_sql(%Q{
    select * from users where id in (
        select to_id   as user_id from messages where from_id = :the_user
        union all
        select from_id as user_id from messages where to_id   = :the_user
    )
}, :the_user => the_user_in_question.id)

An SQL UNION simply does a set-wise union of the two result sets so the above will grab all the users that the_user_in_question has sent messages to and combine that with the users that have sent messages to the_user_in_question; the result will be all the users that are involved in conversations with the_user_in_question as an array of User instances. Since there is an IN on the UNION you can use UNION ALL to avoid a little bit of extra work in the UNION.

You'd probably want to wrap that in a class method on Message:

def self.conversations_involving(user)
    User.find_by_sql(%Q{
        select * from users where id in (
            select to_id   as user_id from messages where from_id = :the_user
            union all
            select from_id as user_id from messages where to_id   = :the_user
        )
    }, :the_user => user.id)
end

And then you could just say things like:

@other_users = Message.conversations_involving(current_user)

in your controller.

You'll want to add indexes to messages.from_id and messages.to_id in your database too.