[Feature... review?] Limited Eager Loading

Currently, eager loading fetches all associated records. It can do this via multiple selects or a JOIN, but the behavior is the same either way.

some cases, this could be undesirable. On my website, for example, we have collections of images, and would like to show a list of collections, along with their top 5 most recently added images. Right now we use multiple queries (one per collection) to do that, as collections can get very large, so we don’t want to waste time loading them all into memory.

It’s possible to do this fetch in a single query using window functions by filtering on dense_rank(). I think this would be a nice feature to have in rails, but I have two concerns.

One deciding on an API. There’s a few possible options I can see for this, and all have pros and cons:

Collection.all.includes(:images).order_associated(images: { created_at: :desc }).limit_associated(images: 5)

Collection.all.includes(:images).order(images: {created_at: :desc}).limit(images: 5)

This is probably the worst idea ever but I thought I’d include it:

imgs = Image.order(created_at: :desc).limit(5)
Collection.all.includes(images: imgs)

other issue has to do with support. Right now, window functions are not
supported on MySQL (but this particular use case can apparently be kind-of-sort-of implemented with row_num) or SQLite, so 2/3rds of the databases Rails supports wouldn’t be able to use this feature directly. It could be implemented in application code, but that would remove most of the benefits (and, in some cases, actually make requests slower).

this feature sound worthwhile despite these issues? If so, I can implement it. I just figured it would be a good idea to ask first if it seemed like a good idea first, since it would probably be a fairly large change.