7

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))
)
Yaroslav Melnichuk
  • 820
  • 1
  • 11
  • 15

1 Answers1

13

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

Lücks
  • 3,806
  • 2
  • 40
  • 54
coleifer
  • 24,887
  • 6
  • 60
  • 75
  • Thanks for answer @coleifer. I tried your suggestion, but I get an error. Peewee generates wrong sql with this join condition – Yaroslav Melnichuk Mar 28 '15 at 22:10
  • 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?) – Yaroslav Melnichuk Mar 28 '15 at 22:19
  • 1
    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! – coleifer Mar 30 '15 at 00:03