Negated .contains() with many-to-many relationships doesn't work

[Former user]

I'm using SQLAlchemy 0.5.4p2.

Using the .contains() method on a many-to-many relationship does not give expected results in the following situation: 1. The .contains() clause is part of an AND/OR clause. 2. The AND/OR clause is negated.

I believe this is due to .contains() adding JOINs to the "outer" SQL statement (i.e. not inside a nested query), which do not behave well when the clause is negated.

An easy workaround is to use .any(MyClass.id == my_obj.id) instead of .contains(my_obj), but it's not as pretty.

I'm attaching a patch to test/orm/test_query.py that exposes this issue.

[Former user]

Test case patch

[Mike Bayer]

well the more reasonable workaround would be to use or_(~Node.children.contains(n4), ~literal(True)). as it turns out an EXISTS query is not as efficient as a straight join for a positive match so its not really an option for the contains() operator to use that in the positive situation (where any() is the more flexible but less efficient operator).

another option might be that contains() magically figures out its context but I'm not sure how magical that might end up being (or even how to implement it without significant complexity), or if it will lead to less efficient queries in some cases.

what we're trying to do here is overlap two different kinds of expression systems and I tend to lean towards the side of manual intervention rather than magic.

[Former user]

When I ran into this issue, I was building the SQL query from the inside out, where the code that generates the AND is unrelated from the code that generates the NOT. I guess I could refactor my generation code to propagate NOTs to leaf clauses, but...meh. any() works well enough for my purposes, so I'm happy.

It would be nice if the ORM tutorial documented the trade-offs between any() and contains().

[Mike Bayer]

IMHO these docs are here now http://www.sqlalchemy.org/docs/orm/internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains