Referencing other tables in a qm.Load()

[yourtallness]

Hello,

I'm trying to eager load a has-many-through association but find it difficult to apply conditions that refer to other tables.

I've seen the examples of Limit(1) (on Youtube) or Where("some_column = false") (in the repo README), but these are not dependent on other tables.

e.g. Assume a site has many Blogs, each Blog has many Posts and each post has many Comments.

Let's say I want to load all Blogs, but for each Blog only the featured Post referenced by the blogs.featured_post_id and for each such Post only the latest Comment (created_at DESC).

I'd start to write something like this:

blogs := models.Blogs(
qm.Where("active = ?", true), // some blog criteria
qm.Loads("Posts", qm.Something), // only the featured post per blog
qm.Loads("Posts.Comments", qm.somethingElse) // only the latest comment per post
).
All(ctx, db)

This means the Post Load needs to refer to the Blog table and the Comment Load needs to refer to the Post table.

For the featured criterion, I'd need to keep only the Posts where blogs.featured_post_id = posts.id.

For the date criterion, in SQL I'd use a self-join with a sub-select like described here to get one latest Comment per Post, by ordering by created_at.

As far as I can tell, since each eager load is done in a subsequent query, references to tables of the original query are not in scope.

How would I tackle the above issue? I would like to be able to use the following way of getting from Blog to Comment elsewhere in my mode (e.g. in my serializer).

comment := blog.R.Posts[0].R.Comments[0]

Note: qm.Loads("Comments", OrderBy("created_at DESC"), qm.Limit(1)) does not solve the date case because it will apply the limit to the entire query, not scoped to each Post id.

Thanks in advance

[aarondl]

This is an issue with the current version of eager loading and unfortunately there's no way around it at this current moment. Your only option is to use a raw query for things that get too complicated.