如何使用 NOTIN 子句在 sql 炼金 ORM 查询

如何将下面的 mysql 查询转换为 sql 炼金术？

SELECT * FROM `table_a` ta, `table_b` tb where 1
AND ta.id = tb.id
AND ta.id not in (select id from `table_c`)

到目前为止，我有这个为 sql 炼金术:

query = session.query(table_a, table_b)
query = query.filter(table_a.id == table_b.id)
87455 次浏览
小开

Try this:

subquery = session.query(table_c.id)
query = query.filter(~table_a.id.in_(subquery))

Note: table_a, table_b and table_c should be mapped classes, not Table instances.

小开

here is the full code:

#join table_a and table_b
query = session.query(table_a, table_b)
query = query.filter(table_a.id == table_b.id)


# create subquery
subquery = session.query(table_c.id)
# select all from table_a not in subquery
query = query.filter(~table_a.id.in_(subquery))
小开

The ORM internals describe the not_in() operator (previously notin_()), so you can say:

query = query.filter(table_a.id.not_in(subquery))
#                               ^^^^^^

From the docs:

inherited from the ColumnOperators.not_in() method of ColumnOperators

implement the NOT IN operator.

This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).

Note that version 1.4 states:

The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

So you may find notin_() in some cases.


提交答案