Django reverse m2m query

user_75103 0
Viewed 263

Using the models from https://docs.djangoproject.com/en/dev/topics/db/queries/#making-queries with minor modifications:

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

I would like to create a dictionary from Author to Entries, where the Author joined this year, and the Entry has a rating of 4 or better. The structure of the resulting dict should look like:

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

while hitting the database less than 3'ish times (or at least not proportional to the number of Authors or Entries).

My first attempt (db hits == number of authors, 100 authors 100 db-hits):

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

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

second attempt, trying to read entries in one go:

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

this one is even worse, 100 authors, 198 db-hits (the original second attempt used {e for e in entries if author in e.authors}, but Django wouldn't have it.

The only method I've found involves raw-sql (4 db-hits):

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

(apologies for using string substitutions in the c.execute(..) call -- I couldn't find the syntax sqlite wanted for a where in ? call).

Is there a more Djangoesque way to do this?

I've created a git repo with the code I'm using (https://github.com/thebjorn/revm2m), the tests are in https://github.com/thebjorn/revm2m/blob/master/revm2m/sampleapp/tests.py

django python
1 Answers

You can use a Prefetch-object [Django-doc] for that:

from django.db.models import Prefetch

good_ratings = Prefetch(
    'entry_set',
    queryset=Entry.objects.filter(rating__gte=4),
    to_attr='good_ratings'
)

authors = Author.objects.filter(
    joined__year=date.today().year
).prefetch_related(
    good_ratings
)

Now the Author objects in authors will have an extra attribute good_ratings (the value of the to_attr of the Prefetch object) that is a preloaded QuerySet containing the Entrys with a rating greater than or equal to four.

So you can post-process these like:

res = {
    author: set(author.good_ratings)
    for author in authors
}

Although since the Author objects (from this QuerySet, not in general), already carry the attribute, so there is probably not much use anyway.

user_675790
Related