如何根据烧瓶sqlalchemy中的某些约束查询多对多? [英] How to query many-to-many based on some constraints in flask sqlalchemy?

问题描述

如果我有一个User和Item模型，并且它们之间存在多对多关联，那么我该如何构建一个返回以下内容的查询:

If I have a User and Item model, and they have a many-to-many association with each other, how do I build a query that returns:

(1)属于名为鲍勃"的任何用户的所有项目

(1) All items that belong to any user named 'Bob'

我尝试过:

Item.query.filter(User.name == 'Bob')

哪个返回所有项目，与用户名无关(不正确)

Which returns all items regardless of the user's name (incorrect)

(2)所有名称为"shark"且属于任何名为"Bob"的用户的项

(2) All items that have the name 'shark' and belong to any user named 'Bob'

我尝试过:

Item.query.filter(User.name == 'Bob' & Item.name == 'shark')

与上述相同，但是无论用户名如何，都仅返回名为"shark"的项目. (不正确)

Same as above, but only returns items named 'shark' regardless of the user's name. (incorrect)

我的模型定义:

association_table = Table('items_users',
    Column('itemid', Integer, ForeignKey('item.id'), primary_key=True),
    Column('userid', Integer, ForeignKey('user.id'), primary_key=True)
)

class Item(Model):
    # other fields...

    # many to many association
    users = relationship('User', secondary=association_table, lazy='dynamic', backref=backref('items', lazy='dynamic'))

class User(Model):
    # other fields...

两个查询的合适语法是什么?

What would be appropriate syntax for two queries?

推荐答案

您需要连接要查询的表，以便过滤一个表将过滤与另一个表关联的组合行.由于已经定义了两个模型之间的关系，因此可以在其上进行联接，而不必手动指定联接条件.

You need to join the tables you will query, so that filtering one will filter the combined row associated with the other. Since you have defined a relationship between the two models, you can join on it rather than specifying a join condition manually.

Item.query.join(Item.users).filter(User.name == 'bob')
Item.query.join(Item.users).filter(User.name == 'bob', Item.name == 'shark')

综合SQLAlchemy文档中的教程.

这篇关于如何根据烧瓶sqlalchemy中的某些约束查询多对多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！