Django reverse m2m查询

使用中的模型 https://docs.djangoproject.com/en/dev/topics/db/queries/#making-queries 稍作修改:

from django.db import models

class Blog(models.Model):
    name = models.CharField(max_length=100)

class Author(models.Model):
    name = models.CharField(max_length=200)
    joined = models.DateField()

    def __str__(self):
        return self.name

class Entry(models.Model):
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
    headline = models.CharField(max_length=255)
    authors = models.ManyToManyField(Author)
    rating = models.IntegerField()

author_entries = {author1: [set of entries], author2: [set of entries], etc.}

而命中数据库的次数少于3次(或者至少与作者或条目的数量不成正比)。

    res = {}
    authors = Author.objects.filter(joined__year=date.today().year)

    for author in authors:
        res[author] = set(author.entry_set.filter(rating__gte=4))

第二次尝试,尝试一次性读取条目:

    res = {}
    authors = Author.objects.filter(joined__year=date.today().year)
    entries = Entry.objects.select_related().filter(rating__gte=4, authors__in=authors)

    for author in authors:
        res[author] = {e for e in entries if e.authors.filter(pk=author.pk)}

这一次更糟,100个作者,198分贝的点击率(最初的第二次尝试使用) {e for e in entries if author in e.authors} 但Django不会接受的。

我发现的唯一方法涉及原始sql(4 db命中):

    res = {}
    _authors = Author.objects.filter(joined__year=date.today().year)
    _entries = Entry.objects.select_related().filter(rating__gte=4, authors__in=_authors)
    authors = {a.id: a for a in _authors}
    entries = {e.id: e for e in _entries}
    c = connection.cursor()
    c.execute("""
        select entry_id, author_id 
        from sampleapp_entry_authors
        where author_id in (%s)
    """ % ','.join(str(v) for v in authors.keys()))

    res = {a: set() for a in _authors}
    for eid, aid in c.fetchall():
        if eid in entries:
            res[authors[aid]].add(entries[eid])

c.execute(..) call——我找不到sqlite需要的语法 where in ? 电话)。

有没有更像django的方法?

我用我正在使用的代码创建了一个git repo( https://github.com/thebjorn/revm2m ),测试正在进行中 https://github.com/thebjorn/revm2m/blob/master/revm2m/sampleapp/tests.py