Repository navigation
Unable to eager load queries with nested one to many relationships #110
Description
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.
Activity
acarapetis commentedon Aug 20, 2024
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 simpleselect(User).options(joinedload(User.contacts))should produce the same error?For now, though,
selectinloadmight work for you?sashahilton00 commentedon Aug 20, 2024
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 commentedon Aug 21, 2024
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 commentedon Aug 25, 2024
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:
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.