1

I am trying to implement one to one thread based private messaging in Rails.

Following is my Message model:

Table name: messages

id                :integer         not null, primary key
sender_id         :integer
recipient_id      :integer
sender_deleted    :boolean         default(FALSE)
recipient_deleted :boolean         default(FALSE)
subject           :string(255)
body              :text
read_at           :datetime
created_at        :datetime
updated_at        :datetime
messagethread_id  :integer

I create a new thread for a new conversation between any two users.

I am using following query to find the latest undeleted message of each thread for a given user (example: with id = 1):

SELECT * 
FROM messages LEFT JOIN 
          (SELECT * 
               FROM messages m3 
               WHERE (m3.sender_id = 1 AND m3.sender_deleted = FALSE) 
                     OR (m3.recipient_id = 1 and m3.recipient_deleted = FALSE)) AS m2 
          ON (messages.updated_at, messages.id) < ( m2.updated_at, m2.id) 
                  AND m2.messagethread_id = messages.messagethread_id
WHERE ((messages.sender_id = 1 AND messages.sender_deleted = FALSE) OR (messages.recipient_id = 1 AND messages.recipient_deleted = FALSE)) AND m2.updated_at IS NULL

I would like to implement this in my rails model/controller and display the results on corresponding view.

Can someone please guide on how to get these results in an array of Messages? Thanks in advance!

Nirav Shah
  • 689
  • 6
  • 16
  • 1
    Are you using find_by_sql? if yes then I'd look at [this](http://guides.rubyonrails.org/active_record_querying.html) – errorhandler Jun 18 '11 at 06:39
  • hi.. I dont mind using find_by_sql. Are there any cons of using it? – Nirav Shah Jun 18 '11 at 06:43
  • no, but it is highly recommended to use the ActiveRecord query interface. it handles alot for you and is one of the main parts of rails – errorhandler Jun 18 '11 at 06:44
  • 1
    I find that the ActiveRecord query interface breaks down rapidly as the query gets more complex; once you start needing sub-selects, derived tables, or CTEs then going straight to SQL is your best bet. For something like that, [`find_by_sql`](http://api.rubyonrails.org/classes/ActiveRecord/Base.html#method-c-find_by_sql) makes a lot of sense. The only con that I can think of is that it is SQL that has to be maintained by hand and a lot of people are scared of SQL. – mu is too short Jun 18 '11 at 06:59
  • I implemented it using find_by_sql and it works great.. thanks! – Nirav Shah Jun 18 '11 at 07:02
  • Sure. You can type it as an answer, I will accept it. Doesn't matter that much to me either. :) Thanks! – Nirav Shah Jun 19 '11 at 06:49

0 Answers0