由 django 中的 prefetch_related 完成的烧瓶多对多连接 [英] flask many to many join as done by prefetch_related from django

问题描述

我在带有 Sql Alchemy ORM 的烧瓶中有以下组和联系人模型

I have following Group and Contact model in flask with Sql Alchemy ORM

group_contact = db.Table(
    'group_contact',
    db.Column('group_id', db.Integer, db.ForeignKey(
        'group.id')),
    db.Column('contact_id', db.Integer, db.ForeignKey(
        'contact.id')),
    db.PrimaryKeyConstraint('group_id', 'contact_id')
)


class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))


class Contact(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    phone = db.Column(db.String(15), nullable=False, unique=True)
    groups = db.relationship(
        "Group", secondary=group_contact, backref='contacts')

现在我需要查询与群组的联系人:

Now I need to query Contact with groups:

contacts = Contact.query.join(Group, Contact.groups).all()
for contact in contacts:
    print(contact.groups)

这里的问题是当我执行上述代码时，SQL 查询的数量随着联系数量的增加而增加.

Here the problem is number of SQL query increases as number of contact increases when I execute above code.

Django ORM 有 prefetch_related() 和 queryset，它根据 django 文档.

Django ORM has prefetch_related() with queryset which does the following according to django docs.

prefetch_related 另一方面，对每个关系进行单独的查找，并在 Python 中进行连接".除了 select_related 支持的外键和一对一关系之外，这允许它预取使用 select_related 无法完成的多对多和多对一对象.

prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python. This allows it to prefetch many-to-many and many-to-one objects, which cannot be done using select_related, in addition to the foreign key and one-to-one relationships that are supported by select_related.

现在我正在尝试通过以下代码对 Sql Alchemy 做同样的事情:

Now I am trying to do the same thing with Sql Alchemy by the following code:

contacts = Contact.query.all()     
contact_groups = group_contact.query.join(
    Group
).filter(group_contact.contact_id.in_([item.id for item in contacts]))

但这给了我这个错误:

AttributeError: 'Table' object has no attribute 'query'

如何使用 SqlAlchemy 从 django 获取 prefetch_related 之类的功能?

How can I get prefetch_related like feature from django with SqlAlchemy?

推荐答案

您想通过使用 关系加载技术.可以告诉 SQLAlchemy 在单个查询中加载组和联系人.

You want to tell SQLAlchemy to eagerly load related objects by using a relationship loading technique. SQLAlchemy can be told to load the groups together with the contacts in a single query.

对于这一个查询，您可以添加joinedload() 选项(可通过 Flask-SQLAlchemy db 对象获得):

For just this one query, you can add joinedload() option (it is available via the Flask-SQLAlchemy db object):

contacts = Contact.query.options(db.joinedload(Contact.groups)).all()

这会在每个匹配的联系人上预加载 Contact.groups 属性:

This pre-loads the Contact.groups attribute on each matched contact:

for contact in contacts:
    # no new query issued to fetch groups, the data for the groups
    # is already available
    print(contact.groups)

执行的查询如下所示:

SELECT 
    contact.id AS contact_id,
    contact.phone AS contact_phone,
    group_1.id AS group_1_id,
    group_1.name AS group_1_name
FROM contact 
LEFT OUTER JOIN (
    group_contact AS group_contact_1
    JOIN "group" AS group_1 ON group_1.id = group_contact_1.group_id
) ON contact.id = group_contact_1.contact_id

您还可以为模型上的关系设置默认加载策略；要始终急切地加载组，请在关系上使用 lazy='joined':

You can also set a default loading strategy for the relationship on the model; to always eagerly load groups, use lazy='joined' on the relationship:

class Contact(db.Model):
    # ...
    groups = db.relationship(
        "Group", secondary=group_contact, backref='contacts',
        lazy='joined')

这篇关于由 django 中的 prefetch_related 完成的烧瓶多对多连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！