In SQLAlchemy, is there a way to eager-load multiple aliased selectables of the same class using one query?

Question

I have an SQLAlchemy mapped class MyClass, and two aliases for it. I can eager-load a relationship MyClass.relationship on each alias separately using selectinload() like so:

alias_1, alias_2 = aliased(MyClass), aliased(MyClass)
q = session.query(alias_1, alias_2).options(
    selectinload(alias_1.relationship),
    selectinload(alias_2.relationship))

However, this results in 2 separate SQL queries on MyClass.relationship (in addition to the main query on MyClass, but this is irrelevant to the question). Since these 2 queries on MyClass.relationship are to the same table, I think that it should be possible to merge the primary keys generated within the IN clause in these queries, and just run 1 query on MyClass.relationship.

My best guess for how to do this is:

alias_1, alias_2 = aliased(MyClass), aliased(MyClass)
q = session.query(alias_1, alias_2).options(
    selectinload(MyClass.relationship))

But it clearly didn't work:

sqlalchemy.exc.ArgumentError: Mapped attribute "MyClass.relationship" does not apply to any of the root entities in this query, e.g. aliased(MyClass), aliased(MyClass). Please specify the full path from one of the root entities to the target attribute.

Is there a way to do this in SQLAlchemy?

Answer 1

So, this is exactly the same issue we had. This docs explains how to do it.

You need to add selectin_polymorphic. For anyone else if you are using with_polymorphic in your select then remove it.

from sqlalchemy.orm import selectin_polymorphic

query = session.query(MyClass).options(
    selectin_polymorphic(MyClass, [alias_1, alias_2]),
    selectinload(MyClass.relationship)

)