如何让Django ManyToMany'通过'查询更有效率？

I'm using a ManyToManyField with a 'through' class and this results in a lot of queries when fetching a list of things. I'm wondering if there's a more efficient way.

我正在使用带有'through'类的ManyToManyField,这在获取事物列表时会导致大量查询。我想知道是否有更有效的方法。

For example here are some simplified classes describing Books and their several authors, which goes through a Role class (to define roles like "Editor", "Illustrator", etc):

例如,这里有一些描述Books及其几个作者的简化类,它们通过Role类(定义“Editor”,“Illustrator”等角色):

class Person(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)

    @property
    def full_name(self):
        return ' '.join([self.first_name, self.last_name,])

class Role(models.Model):
    name = models.CharField(max_length=50)
    person = models.ForeignKey(Person)
    book = models.ForeignKey(Book)

class Book(models.Model):
    title = models.CharField(max_length=255)
    authors = models.ManyToManyField(Person, through='Role')

    @property
    def authors_names(self):
        names = []
        for role in self.role_set.all():
            person_name = role.person.full_name
            if role.name:
                person_name += ' (%s)' % (role.name,)
            names.append(person_name)
        return ', '.join(names)

If I call Book.authors_names() then I can get a string something like this:

如果我调用Book.authors_names()然后我可以得到这样的字符串:

John Doe (Editor), Fred Bloggs, Billy Bob (Illustrator)

John Doe(编辑),Fred Bloggs,Billy Bob(插画家)

It works fine but it does one query to get the Roles for the book, and then another query for every Person. If I'm displaying a list of Books, this adds up to a lot of queries.

它工作正常,但它执行一个查询以获取该书的角色,然后为每个人执行另一个查询。如果我显示一个书籍列表,这会增加很多查询。

Is there a way to do this more efficiently, in a single query per Book, with a join? Or is the only way to use something like batch-select?

有没有办法在每本书的单个查询中使用连接更有效地执行此操作?或者是使用批量选择之类的东西的唯一方法?

(For bonus points... my coding of authors_names() looks a bit clunky - is there a way to make it more elegantly Python-esque?)

(对于奖励积分......我对authors_names()的编码看起来有点笨拙 - 有没有办法让它更优雅地使用Python-esque?)

2 个解决方案

#1

This is a pattern I come across often in Django. It's really easy to create properties such as your author_name, and they work great when you display one book, but the number of queries explodes when you want to use the property for many books on a page.

这是我经常在Django遇到的模式。创建诸如author_name之类的属性非常容易,并且当您显示一本书时它们可以很好地工作,但是当您想要在页面上使用该属性用于许多书籍时,查询的数量会爆炸。

Firstly, you can use select_related to prevent the lookup for every person

首先,您可以使用select_related来阻止查找每个人

  for role in self.role_set.all().select_related(depth=1):
        person_name = role.person.full_name
        if role.name:
            person_name += ' (%s)' % (role.name,)
        names.append(person_name)
    return ', '.join(names)

However, this doesn't solve the problem of looking up the roles for every book.

但是,这并没有解决查找每本书角色的问题。

If you are displaying a list of books, you can look up all the roles for your books in one query, then cache them.

如果要显示书籍列表,则可以在一个查询中查找书籍的所有角色,然后缓存它们。

>>> books = Book.objects.filter(**your_kwargs)
>>> roles = Role.objects.filter(book_in=books).select_related(depth=1)
>>> roles_by_book = defaultdict(list)
>>> for role in roles:
...    roles_by_book[role.book].append(books)

You can then access a book's roles through the roles_by_dict dictionary.

然后,您可以通过roles_by_dict字典访问图书的角色。

>>> for book in books:
...    book_roles = roles_by_book[book]

You will have to rethink your author_name property to use caching like this.

您将不得不重新考虑您的author_name属性以使用这样的缓存。

I'll shoot for the bonus points as well.

我也会为奖励积分拍摄。

Add a method to role to render the full name and role name.

向角色添加方法以呈现全名和角色名称。

class Role(models.Model):
    ...
    @property
    def name_and_role(self):
        out = self.person.full_name
        if self.name:
            out += ' (%s)' % role.name
        return out

The author_names collapses to a one liner similar to Paulo's suggestion

author_names折叠成一个类似于Paulo的建议

@property
def authors_names(self):
   return ', '.join([role.name_and_role for role in self.role_set.all() ])

#2

I would make authors = models.ManyToManyField(Role) and store fullname at Role.alias, because same person can sign books under distinct pseudonyms.

我会创建authors = models.ManyToManyField(Role)并将全名存储在Role.alias中,因为同一个人可以使用不同的假名来签名。

About the clunky, this:

关于笨重,这:

def authors_names(self):
    names = []
    for role in self.role_set.all():
        person_name = role.person.full_name
        if role.name:
            person_name += ' (%s)' % (role.name,)
        names.append(person_name)
    return ', '.join(names)

Could be:

def authors_names(self):
   return ', '.join([ '%s (%s)' % (role.person.full_name, role.name) 
                 for role in self.role_set.all() ])

#1