通过sqlalchemy中的复合字段过滤

Question

如何使用sqlalchemy的ORM通过多个字段过滤？

示例：

SELECT p.* FROM product p
WHERE (p.barcode, p.partner) NOT IN (
    SELECT barcode, partner
    FROM unallowed_products
);

Answer 1

让我们定义一些别名，以便在查看可能的解决方案之前减少冗长：

P, U = Product, UnallowedProduct

版本1：外部联接 null

这是非常通用的解决方案，应与所有后端（RDBMS）一起使用

q = (
    session.query(P)
    .outerjoin(U, and_(P.barcode == U.barcode, P.partner == U.partner))
    .filter(U.id == None)
)

版本2：元组比较

这对所有后端都不起作用，但应适用于mysql，postgresql请参阅tuple_文档

q = (
    session.query(P)
    .filter(~tuple_(P.barcode, P.partner).in_(
        select([U.barcode, U.partner])
    ))
)

版本3：使用多个或语句

请参阅此答案。没有理由将其用作版本1和2版是清洁的。

版本4：条形码和合作伙伴的串联成列

请参阅此问题。这基本上是您自己的解决方案。同样，没有理由将其使用，因为其他版本更干净，并且不需要转换为字符串等。

Answer 2

此查询应解决问题：

SELECT p.* FROM product p
WHERE (p.barcode || "_" || p.partner) NOT IN (
    SELECT barcode || "_" || partner
    FROM unallowed_products
);

可以像这样翻译成sqlalchemy orm：

subquery = session.query(
    cast(UnallowedProducts.barcode, type_=Text) +
    '_' +
    cast(UnallowedProducts.partner, type_=Text)
).subquery()
session.query(Product)
    .filter((
              cast(Product.id, type_=Text) +
              '_' +
              cast(Product.partner, type_=Text)
            ).notin_(subquery))

这个棘手的解决方案几乎适用于每种情况。