0

I've searched a lot here but couldn't find a similar topic: i need to write a Query in Arel because i use will_paginate to browse through the results, so i'd loose much comfort in implementation with raw SQL.

Here's what i need to spell in Arel:

SELECT m.*
 FROM   messages m
 JOIN  (SELECT tmp.original_id as original_id,
               max(tmp.id) as id
        FROM   messages tmp
        WHERE  tmp.recipient_id = ?
        GROUP BY tmp.original_id) g ON (m.id = g.id)
 ORDER BY m.updated_at DESC;

Explained in short words: the subquery retrieves all messages for a user. If a message has newer replies (in replies i save the refering message id as original_id) the older ones will be ignored. For the result of all these messages i want Rails to deliver me the correpsonding objects.

I'm quite skilled in SQL but unfortunately not with Arel. Any help would be kindly appreciated.

Stefan
  • 317
  • 1
  • 3
  • 11

1 Answers1

0

How about something like this? 

class Message < ActiveRecord::Base
  scope :newer_messages lambda { |num_days=10| where("updated_at > #{Time.now} - #{num_days}.days") }
end

You can then find newer messages for a given user like this:

Message.find_by_user_id(user_id).newer_messages
Salil
  • 9,534
  • 9
  • 42
  • 56
  • Interesting idea. Although this may work i chose another option for performance reasons and let the database precalculate the newest reply by a trigger function. Thanks for your effort. – Stefan Apr 21 '12 at 14:19
  • There is only one select query issued to the database when you chain calls like this. (Because they execute under one scope). You can observe that from the server log. I am just mentioning this in case you are thinking that it is issuing multiple select queries. – Salil Apr 22 '12 at 15:19