this kind of thing is achieved with an OUTER JOIN. you can put everything to do with TableB and the join into the ON clause:
session.query(TableA).outerjoin(TableB, and_(TableA.col_fk_id == TableB.col, TableB.col == 1)) On May 8, 2014, at 2:32 PM, Joseph Casale <[email protected]> wrote: > I have scenario where I have a TableA that contains rows with with an > optional reference to TableB. > > That foreign key column in TableA has triggers and constraints enforcing > either one null or many > with unique references. > > When I query for rows I need to select either rows with a reference that > applies to specific row > (there may be many rows with different references, but only one applies) or > choose the null row. > > I use: > > session.query(TableA).\ > filter( > (TableA.col_fk_id == session.query(TableB.id).filter(TableB.col > == 1)) | > (TableA.col_fk_id == null()) > ) > > This grabs the right row in TableB to filter TableA on but I'll admit not > being sufficiently fluent in > SQLAlchemy so I am sure their is a simpler way to do this? Any guidance would > be appreciated. > > Thanks! > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
