Unable to eager load queries with nested one to many relationships

[sashahilton00]

Currently it is not possible to eager load relationships on a query against an entity that has a one to many relationship using sqlalchemy 2.0 style queries.

As an example:

q = select(User).where(*query_conditions).join(User.contacts).options(contains_eager(User.contacts).contains_eager(Contact.addresses)).order_by(User.id)

current_page = await select_page(s, q, per_page=results_per_page, page=page_marker)

where User -> Contact is a one to many relationship, and Contact -> Address is also a one to many relationship, the following error is thrown:

sqlalchemy.exc.InvalidRequestError: The unique() method must be invoked on this Result, as it contains results that include joined eager loads against collections

This is expected as detailed in the tip here, but currently there is no way to call unique on the results from sqlakeyset as the handling of the results from sqlalchemy is done internally by sqlakeyset with no way to override the default handling.

[acarapetis]

I'll look into this when I have a chance. I think the issue should be reproducible without the nesting and the contains_eager, right? Just a simple select(User).options(joinedload(User.contacts)) should produce the same error?

For now, though, selectinload might work for you?

[sashahilton00]

I may have a workaround which I'll post later when I'm back at my desk. I believe selectinload does work.

I didn't try without contains_eager - I just copied out the problematic query as an example of what triggers the behaviour.

[acarapetis]

I've just released a new version that should call .unique() automatically when necessary. Give it a shot with your query and let me know how it goes :)

[sashahilton00]

Unfortunately the latest change doesn't seem to fix the issue. I've spent several hours wrestling with this, mostly to try and keep things relatively performant, and for anyone else looking for a workaround in future, this is what I ended up using:

q = select(User).\
                where(*query_conditions).\
                options(
                selectinload(User.contacts),
                joinedload(User.contacts, innerjoin=True).selectinload(Contact.addresses),
                # joinedload(User.contacts, innerjoin=True).noload(Contact.metadata)
            ).\
                order_by(User.id, User.created_at)

The commented line in there is just as an example of how not to load unnecessary relationships. Also please note that innerjoin has it's own set of assumptions that should be checked in the official docs before usage.