Eager loading association but limit return

Question

It seems like a simple query but I just can't nail it.

Basically it boils down to the age old concept of a categories having posts. The expected associations are in place, a category has_many posts whilst a post belongs to a category.

I want to retrieve all the categories with their posts but limit the number of posts to 10.

Any ideas?

Answer 1

This isn't something that you can do with raw SQL as LIMIT's are on the total dataset size, not anything else.

The only way of doing this purely by SQL is to create a fake id column in your join and filter than when it comes out which is something that implementation wise is incredibly dependant on the database server you are using.

The alternatives as either get all categories and posts and cut the recordset down, or get all categories and iteratively get 10 posts as Joerg suggests.