0

Step 1:

Suppose I have two one->many related models such as:

from django.db import models
class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    name    = models.CharField(max_length=300)
    author  = models.ForeignKey(Author)
    pubdate = models.DateField()

How can I get a list of all authors and the name of their last book (or rather the book object itself)?

  • Without one query per article
  • Some authors may not have published a book yet, I still want them in the list (with None)
  • For portability I want to avoid manual SQL
  • It should not load all Books in memory

Step 2:

I am not sure that is a significant difference in the solution.

Suppose now I have I PictureBook subclass of the Book (using django-polymorphic) and I want to know the the picture_url of all authors last PictureBook (and again None if not).

from polymorphic import PolymorphicModel
class Book(PolymorphicModel):
    name    = models.CharField(max_length=300)
    author  = models.ForeignKey(Author)
    pubdate = models.DateField()

class PictureBook(Book):
    picture_url = models.CharField(max_length=200)

Unfortunately the examples in the documentation about annotate only show direct Min/Max values. If I would start with all PictureBooks, then I would miss the authors that don't have one. This question is somwhat related, but very old. I am using Django 1.7.

Community
  • 1
  • 1
Zulan
  • 21,896
  • 6
  • 49
  • 109

1 Answers1

1

In your first example, if the relationship were set up like:

from django.db import models
class Author(models.Model):
    name = models.CharField(max_length=100)
    books = models.ManyToMany(Book)

    def latest_book(self):
        """ 
        This is only efficient / accurate if you've prefetched books 
        in the right order!
        """
        if self.books.exists():
            return self.books.all()[0]
        return None


class Book(models.Model):
    name = models.CharField(max_length=300)
    pubdate = models.DateField()

Then with the Prefetch object introduced in 1.7:

from django.db.models import Prefetch
authors = Author.objects.all().prefetch_related(
    Prefetch('books', queryset=Books.objects.order_by('-pubdate')

for author in authors:
    print author.latest_book()

2 database queries, but of course be aware that you are loading every book for every author into memory. That said, using the Prefetch object's queryset arg you could probably make this more efficient.

I made latest_book a member of Author to keep the sample code light, but in reality you'd probably want this outside the Author model's definition, since its not useful outside this specific implementation with the proper prefetch called.

I'm not familiar with the django-polymorphic package, but I'm betting you can use this approach to your advantage, as prefetching supports generic relationships also.

Casey Kinsey
  • 1,451
  • 9
  • 16
  • Sorry - I forgot the ForeignKey in the example code. Can I use prefetch thing also without ManyToMany? Also I must say that loading all Books in memory is probably not an option :-(. – Zulan Aug 29 '14 at 22:45
  • Oh, I misread that it was one to many. That makes things much easier. With a ForeignKey you can simply select_related('books') which executes a SQL JOIN. – Casey Kinsey Aug 29 '14 at 23:17
  • `select_related` seems to work for the many-to-one direction: `Book.objects.select_related("author")`, but the other way around doesn't work for me. `Author.objects.select_related("book")` -> runs, but there is no attribute book on the Author object. – Zulan Aug 30 '14 at 08:44
  • You need to either set the `related_name` kwarg on the ForiegnKey or use the default related name: `Author.objects.select_related('book_set')`. – Casey Kinsey Aug 30 '14 at 13:05
  • Yes, select related works. (later using `author.book_set.get_queryset()...` ). But I suppose it does still have the issue of loading everything in memory. – Zulan Aug 30 '14 at 15:57