I have a User model that has many Post.
I want to get, on a single query, a list of users IDs, ordered by name, and include the ID of their last post.
Is there a way to do this using the ActiveRecord API instead of a SQL query like the following?
SELECT users.id,
(SELECT id FROM posts
WHERE user_id = users.id
ORDER BY id DESC LIMIT 1) AS last_post_id
FROM users
ORDER BY id ASC;
You should be able to do this with the query generator:
User.joins(:posts).group('users.id').order('users.id').pluck(:id, 'MAX(posts.id)')
There's a lot of options on the relationship you can use to get data out of it. pluck is handy for getting values independent of models.
Update: To get models instead:
User.joins(:posts).group('users.id').order('users.id').select('users.*', 'MAX(posts.id) AS max_post_id')
That will create a field called max_post_id which works as any other attribute.
