Is it possible to make sql join on several 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 Items which does not have related UserAnswer records for current user. In SQL it would 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 constraint on two fields using peewee syntax? It will be cool if I can do it in this way:

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

Answer 1

Yes you can join on multiple 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

Sorry there is not an example of using multiple join conditions, but the on is just an arbitrary expression so you can put any valid peewee "Expression" you like there.

Important: you should import JOIN - from peewee import JOIN