CODEWITHSUNDEEP
pythonsqlalchemy
astex
astex

Reputation: 1045

Filters on Joins are not Propagated to the Relevant orm.relations

Defining some basic structures here:

class A( Base ):

    __tablename__ = 'a'
    id            = Column( types.Integer(), primary_key = True )

    abs           = orm.relation( 'AB', lazy='joined' )

class AB( Base ):

    __tablename__ = 'ab'
    id            = Column( types.Integer(), primary_key = True )
    a_id          = Column( types.Integer(), ForeignKey( 'a.id' ) )
    b_id          = Column( types.Integer(), ForeignKey( 'b.id' ) )

    a             = orm.relation( 'A' )
    b             = orm.relation( 'B', lazy='joined' )

class B( Base ):

    __tablename__ = 'b'
    id            = Column( types.Integer(), primary_key = True )

    bas           = orm.relation( 'AB' )

Now say I have an A with multiple Bs related to it (say A.id = 1) and I would like to filter based on these Bs. I do the following query:

a = db.session.query( A ).join( A.abs ).filter( AB.b_id = 1, A.id = 1 ).first()

At this point, I would expect len( a.abs ) == 1, but this is not the case. In other words, the filters applied to the join are not propagated through to the orm.relation. How do I get this behavior?

Upvotes: 1

Views: 55

Answers (1)

javex
javex

Reputation: 7544

The reason for this issue is described in The Zen of Eager Loading which comes down to this: You will have two distinct joins in your query. One is for building the correct join to filter on (which is what you produced with .join(A.abs)) and the other is for loading the relation (which the ORM automatically inserts based on lazy="joined", otherwise it would query it on acess).

Now there are several ways around this. But first you should think about what you really want. Because when you say A.abs you really say "all the AB entries that belong to this A". But when you specify a single b_id than that's not what you want, because that's not what this relationship stands for. So here is the clean way:

db.session.query(A, AB).join(A.abs).filter(AB.b_id = 1, A.id = 1)

Now you get the AB as the second returned object from the query. This is how to do it correctly, because having only one AB in A.abs would actually be lying to the ORM: That's not true here (and it could break stuff). However, if you would insist on doing that it is possible. You can disable the double joins with sqlalchemy.orm.contains_eager:

db.session.query(A).join(A.abs).options(contains_eager(A.abs)).filter(AB.b_id = 1, A.id = 1)

This will yield an A.abs with only one entry which has b_id = 1. However, as already stated that is not a nice solution and not what you should do.

As an additional hint, I suggest you turn on echo=True or even echo="debug" in your engine to make sure you see the queries being executed. If you look at your original query, so will see two joins on the same table.

Upvotes: 1

Related Questions