SqlAlchemy: check if there is one object in any relationship (or_ (object.relationship1.contains (otherObject), object.relationship2.contains (otherObject))

Question

Say I have a class like this:

class Foo(declarativeBase): bars1 = relationship(Bar.Bar, secondary=foos_to_bars1, collection_class=set()) bars2 = relationship(Bar.Bar, secondary=foos_to_bars2, collection_class=list()) 

(Each of the relationships gives me a "Bar" with certain conditions). At some point, I want to get instances of “Foo” that have a “bar” (an instance of Bar.Bar) in any of the relationships.

If I try to do:

 def inAnyBar(bar) query(Foo).filter(or_(Foo.bars1.contains(bar), Foo.bars2.contains(bar)).all() 

I get an empty result.

It looks (to me) as if I am doing something like:

 query(Foo).join(Foo.bars1).filter(Foo.bars1.contains(bar)).\ join(Foo.bars2).filter(Foo.bars1.contains(bar)) 

Since Foo.bars1 does not contain a bar, the second filter gives empty results.

I managed to find a workaround with subqueries (each joins + a filter in the subquery, then or all the subqueries), but I would like to know if there is a better way to do this ...

I found this: http://techspot.zzzeek.org/2008/09/09/selecting-booleans/

This does what I want to do, but it is for SqlAlchemy 0.5, and I am (almost) sure that there is a "cleaner" way to do it with SqlAlchemy 0.6.6

Thanks!

Answer 1

You're right, session.query(Foo).filter(Foo.bars1.contains(bar)|Foo.bars2.contains(bar)) creates the following SQL:

 SELECT "Foo".id AS "Foo_id" FROM "Foo", foos_to_bars1 AS foos_to_bars1_1, foos_to_bars2 AS foos_to_bars2_1 WHERE "Foo".id = foos_to_bars1_1.foo AND ? = foos_to_bars1_1.bar OR "Foo".id = foos_to_bars2_1.foo AND ? = foos_to_bars2_1.bar 

which returns an invalid result when one of the secondary tables is empty. Seems like a bug in SQLAlchemy. However, when replacing contains() with any() , the problem is fixed (it uses EXISTS subqueries):

 session.query(Foo).filter(Foo.bars1.any(id=bar.id)|Foo.bars2.any(id=bar.id)) 

You can also explicitly specify OUTER JOIN:

 Bar1 = aliased(Bar) Bar2 = aliased(Bar) session.query(Foo).outerjoin((Bar1, Foo.bars1)).outerjoin((Bar2, Foo.bars2))\ .filter((Bar1.id==bar.id)|(Bar2.id==bar.id))