detecting whether outerjoin finds a match or not

[Tim Tisdall]

When doing an outerjoin it's possible an object is returned as a None. I wanted to filter rows based on whether one of the objects would be returned or would be a None so I tried something like the following:

DBSession.query(
    TableA,
    TableB
).outerjoin(
    TableB
).filter(
    TableB == None
)

Looking at the resulting SQL, it seems the TableB == None filter is translated into false = 1. TableB !=None seems to translate into 1 = 1.

It seems that the way to get this to work is:

DBSession.query(
    TableA,
    TableB
).outerjoin(
    TableB
).filter(
    TableB.some_primary_key == None
)

Essentially you need to test for None on an attribute that should never be None. However, that seems a little confusing as the result of the query is not a TableB object with that attribute equal to None, but the whole object is instead None.

This issue is a feature request to make the top-most query work as expected. This could be done by translating TableB == None into a test for a NULL value on any of the primary keys of TableB.

[Mike Bayer]

hi! thanks for reporting this. However, in this case we must consider the realities of Python. I'm assuming your "TableB" here is a mapped class, and not a Table object. If SQLAlchemy were to take everyone's mapped classes, and in all cases monkeypatch a special __eq__() method onto all mapped classes implicitly that does something totally unusual, I hope you'll agree people would find that to be very surprising. SQLAlchemy's mechanism of intercepting operators like == relies upon these special methods in order to produce special objects in these cases; in the case of SQLAlchemy's built-in clause objects, this is not controversial, but in the case of mapped classes, it would be overstepping our bounds to assume such a thing.

You are free to create your own __eq__() method on your TableB class that produces the SQL expression that you'd like here, if you really want to go that way.

[Tim Tisdall]

^_^ I thought there might be a reason why this wasn't already done, but not knowing the magic behind sqla I thought I'd post a request any way.

I guess you could do it with a .isNone() sub-method on the mapped class or a 'isNone()' that accepted a mapped class and returned filters checking for NULL's on the primary keys.