Filter by compound fields in SQLAlchemy

Question

How can I filter by multiple fields using SQLAlchemy's ORM?

Example:

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

Answer 1

Lets define some aliases in order to reduce verbosity before looking at possible solutions:

P, U = Product, UnallowedProduct

version-1: OUTER JOIN + WHERE IS NULL

This is quite universal solution and should work with all backends (RDBMS)

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

version-2: tuple comparison

This does not work for all backends, but should work for mySQL, postgresql See tuple_ documentation

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

version-3: use multiple OR statements

See this answer. There is no reason to use it as version-1 and version-2 are cleaner.

version-4: concatenation of barcode and partner into one column

See this question. This is basically your own solution. Again, there is not reason to use it as other versions are much cleaner, and do not require conversions to strings etc.

Answer 2

This query should solve the problem:

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

which can be translated to SQLAlchemy ORM like this:

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))

This tricky solution should work for virtually every case.