Nested preloads with preload query

I’m trying unsuccessfully to create a nested preload that includes a preload query on the first level of preload.

Background: User has many Membership, and Membership belongs to MembershipType. I only care about the most recent Membership for each User, so I have a preload query

mquery = from m in Membership, order_by: [desc: m.inserted_at], limit: 1

That gives me what I want, such that someuser.membership is populated with the single newest membership record. However, I need to get the human details of that membership from the MembershipType table. Thus I need a nested preload.

I can’t work out the syntax, and I’ve tried a lot of (garbage :slight_smile: ).

Repo.all from u in User, preload: [membership: ^mquery, :membership_type]

I’ve seen nice examples of nested preloads, but not yet one with a preload query in the mix. Suggestions or references to good examples are most welcome!

You might try preloading membership_type in mquery:

mquery = 
  from m in Membership, 
    order_by: [desc: m.inserted_at], 
    preload: [:membership_type],
    limit: 1

Repo.all from u in User, preload: [membership: ^mquery]
1 Like

This is great! It does indeed work.

It also leads me to another question: Is there a way I can define my query so it returns a single thing (struct I guess) instead of a list? limit: 1 does limit my results to a single item, but that is still being packaged inside a [] list.

It would be nice if I could do someuser.membership.membership_type, but I cannot do that since the membership query returns a list of one membership.

ADDED:
I suspect this is because my user has_many membership. Structurally this is true, because a user may change their membership at some point, and I want to keep the full history of changes. But practically, I only care about user. (newest) membership as a single thing.

Perhaps Ecto is being smart about has_many and returning [], even though my preload intentionally limits to 1 record.

You may want to consider having multiple relationships on your user for memberships. You could have a has_one :membership and has_many :membership_history or something similar. This would give you the single record you are looking for, while still preserving the history.

1 Like

Sorry for my delay… got pulled off this project and onto something else for a bit.

This is a great suggestion, putting both has_one and has_many. That, plus Axel’s double preload query, gives me exactly what I was searching for :slight_smile:

Thanks!

1 Like