Peewee ORM -- get all rows EXCEPT those shared in another query

Question

I have two queries, available and active.

If I have a row in active, I would want to exclude it from available.

See my attempt below:

active_params = (AvailParam
        .select()
        .join(AvailParamPhones)
        .join(Phone)
        .where(AvailParamPhones.phone == phone)
        .order_by(AvailParam.base_param.name)
        )

avail_params = (AvailParam
                .select()
                .join(AvailParamPhones, JOIN.LEFT_OUTER)
                .where( AvailParam.select() << active_params )
                .order_by(AvailParam.base_param.name)
                )

This gives me

TypeError: unsupported operand type(s) for <<: 'ModelSelect' and 'ModelSelect'

How should I be querying while excluding members of a previous query?

Thanks!

Answer 1

Well, first of all, use "AvailParam.id.in_(active_params)"...it makes no sense to have a select on the left-hand-side and right-hand-side of an IN clause:

avail_params = (AvailParam
                .select()
                .join(AvailParamPhones, JOIN.LEFT_OUTER)
                .where( AvailParam.id.in_(active_params) )
                .order_by(AvailParam.base_param.name))

If you want NOT IN, use AvailParam.id.not_in(...):

avail_params = (AvailParam
                .select()
                .join(AvailParamPhones, JOIN.LEFT_OUTER)
                .where( AvailParam.id.not_in(active_params) )
                .order_by(AvailParam.base_param.name))

If your database supports EXCEPT you can also use a compound select query. Example:

http://docs.peewee-orm.com/en/latest/peewee/query_examples.html#combining-results-from-multiple-queries