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

Question

Let's 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 "Bar"s with a certain conditions). At a certain point, I want to get instances of "Foo"s that have a "bar" (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) like I'm doing something like:

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

Since Foo.bars1 doesn't contain bar, the second filter gives empty results.

I've been able to find a workaround with subqueries (each join+filter in a subquery, then or_ all the subqueries) but I'd like to know if there's a better way to do it...

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

That does what I want to do, but it's for SqlAlchemy 0.5 and I'm (almost) certain that there's a "cleaner" way to do it with SqlAlchemy 0.6.6

Thank you!

Answer 1

You are right, session.query(Foo).filter(Foo.bars1.contains(bar)|Foo.bars2.contains(bar)) produces 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 incorrect result when one of the secondary tables is empty. Seems like a bug in SQLAlchemy. However replacing contains() with any() fixed the problem (it uses EXISTS subqueries):

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

Also you can specify OUTER JOIN explicitly:

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))