[Sqlalchemy-tickets] Issue #3479: detecting whether outerjoin finds a match or not (zzzeek/sqlalche
Brought to you by:
zzzeek
|
From: Tim T. <iss...@bi...> - 2015-07-08 15:18:35
|
New issue 3479: detecting whether outerjoin finds a match or not https://bitbucket.org/zzzeek/sqlalchemy/issues/3479/detecting-whether-outerjoin-finds-a-match 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: ```python 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: ```python 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`. |