61

I need to execute this query::

select field11, field12
from Table_1 t1
left outer join Table_2 t2 ON t2.tbl1_id = t1.tbl1_id
where t2.tbl2_id is null

I had these classes in python:

class Table1(Base):
   ....

class Table2(Base):
   table_id =  Column(
        Integer,
        ForeignKey('Table1.id', ondelete='CASCADE'),
    )
    ....

How do I get to the above from the below?

Nathaniel Ford
  • 20,545
  • 20
  • 91
  • 102
Roosh
  • 964
  • 2
  • 9
  • 15

2 Answers2

101
q = session.query(Table1.field1, Table1.field2)\
    .outerjoin(Table2)\ # use in case you have relationship defined
    # .outerjoin(Table2, Table1.id == Table2.table_id)\ # use if you do not have relationship defined
    .filter(Table2.tbl2_id == None)

should do it, assuming that field1 and field2 are from Table1, and that you define a relationship:

class Table2(Base):
    # ...
    table1 = relationship(Table1, backref="table2s")
van
  • 74,297
  • 13
  • 168
  • 171
  • 1
    Is there a way you could do this if Table1 and Table2 are the same tables? – avyfain Sep 01 '15 at 18:01
  • 2
    Sure you can. Read [Using Aliases](http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#using-aliases) section of the documentation. – van Sep 01 '15 at 18:43
20

You can also do that using SQLAlchemy Core only:

session.execute(
    select(['field11', 'field12'])
    .select_from(
        Table1.outerjoin(Table2, Table1.tbl1_id == Table2.tbl1_id))
    .where(Table2.tbl2_id.is_(None))
)

PS .outerjoin(table, condition) is equivalent to .join(table, condition, isouter=True).

maciek
  • 3,198
  • 2
  • 26
  • 33
  • 4
    Also `Table1.outerjoin(Table2, Table1.tbl1_id == Table2.tbl1_id)`. – Ilja Everilä Jun 06 '18 at 11:32
  • Why do you need the where clause here? Doesn't that limit you to just those records where Table2 has a null in ID? If you don't include the where clause do you end up with a full outer join? – Ben Jan 19 '19 at 20:45
  • See the question. @Roosh wanted to filter the field out. For full outer join you should add full=True to the call. – maciek Jan 19 '19 at 23:46