是否可以使用 peewee python ORM 在多个字段上进行 sql join? [英] Is it possible to make sql join on several fields using peewee python ORM?

问题描述

假设我们有这三个模型.

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

我想获取当前用户没有相关 UserAnswer 记录的所有 Items.在 SQL 中，它会是这样的:

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;

是否可以使用 peewee 语法对两个字段进行带约束的左外连接?如果我能这样做就很酷了:

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

推荐答案

是的，您可以在多个条件下加入:

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

此处的文档:http://docs.peewee-orm.com/en/latest/peewee/api.html#Query.join

抱歉，没有使用多个连接条件的示例，但是 on 只是一个任意表达式，因此您可以在其中放置任何您喜欢的有效 peewee表达式".

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.

重要提示:您应该导入 JOIN - from peewee import JOIN

Important: you should import JOIN - from peewee import JOIN

这篇关于是否可以使用 peewee python ORM 在多个字段上进行 sql join?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！