Is it possible to make a sql connection in multiple fields using peewee python ORM?

Question

Assuming we have these three models.

class Item(BaseModel):
    title = CharField()

class User(BaseModel):
    name = CharField()

class UserAnswer(BaseModel):
    user = ForeignKeyField(User, 'user_answers')
    item = ForeignKeyField(Item, 'user_answers_items')
    answer = ForeignKeyField(Item, 'user_answers')

I want to get all Itemsthat have no related records UserAnswerfor the current user. In SQL, it will be something like this:

select * from item i
left join useranswer ua on ua.item_id=i.id and ua.user_id=1
where ua.id is null;

Is it possible to make a left outer join with a restriction on two fields using peewee syntax? It will be great if I can do it like this:

Item.select().join(UserAnswer, JOIN_LEFT_OUTER, on=['__my_constraints_here__']).where(
    (UserAnswer.id.is_null(True))
)

Answer 1

Yes, you can join several conditions:

join_cond = (
    (UserAnswer.item == Item) &
    (UserAnswer.user == 1))
query = (Item
         .select()
         .join(
             UserAnswer,
             JOIN_LEFT_OUTER,
             on=join_cond))
         .where(UserAnswer.id.is_null(True)))

Docs here: http://docs.peewee-orm.com/en/latest/peewee/api.html#Query.join

, , on , peveee "Expression", .