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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs