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

Venator

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

Minyan

Thanks for answer @coleifer. I tried your suggestion, but I get an error. Peewee generates wrong sql with this join condition – Venator

It makes this sql SELECT 't1'.'id', 't1'.'description' FROM 'item' AS t1 LEFT OUTER JOIN 'useranswer' AS t2 ON (('t2'.'item_id' = 'item' AS t1) AND ('t2'.'user_id' = %s)) WHERE ('t2'.'id' IS %s) [1, None]. It has an error in on clause ON (('t2'.'item_id' = 'item' AS t1). It must be ON (('t2'.'item_id' = 't1'.'id'). What I must do to make it work?) – Venator

Then make it be (UserAnswer.id == 1) instead of (UserAnswer.user == 1). I bet if you looked at it and thought about it you'd have been able to figure it out! – Minyan