Rails - Apr 2010 - Latest user post (many-to-many relation)

Hello

I have a one-to-many relation between two tables, ''users'' and
''posts'',
where User has_many :posts and Post belongs_to :user. I have a query
which returns every user on the table, along with the content of their
latest post, if any, as follows:

    @users = User.find_by_sql <<-EOQ
      SELECT
        u.name, p1.content
      FROM
        users u
      LEFT JOIN posts p1 ON (u.id = p1.user_id)
      LEFT OUTER JOIN posts p2 ON
        (u.id = p2.user_id AND
         (p1.created_at < p2.created_at OR
          p1.created_at = p2.created_at AND p1.id < p2.id))
      WHERE p2.id IS NULL
    EOQ

Is there a way to make this more idiomatic by using Active Record''s
query interface? I''m using Rails 3.

Thanks,
Andre

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.