CODEWITHSUNDEEP
django
user1156093
user1156093

Reputation: 35

Django complicated queryset

i am trying to figure out how to solve this problem without any luck. The situation is that Author has many books divided by genres and i would like to have that when i query author it would return author and book objects divided by genres.

Author object would have these properties:
    name
    fantasy - would have one book based by given date
    crime   - would have one book based by given date
    romance - would have one book based by given date

Is there a sane way to achieve this by not making thousands(if i would have that many genres) of foreign keys in author model?

class Author(models.Model):
    name = models.CharField(u'Name',max_length=100)

GENRE = (
    (0,u'Fantasy'),
    (1,u'Crime'),
    (2,u'Romance')
)

class Book(models.Model):
    author  = models.ForeignKey(Author)
    name    = models.CharField(u'Name',max_length=100)
    genre   = models.SmallIntegerField(u'Genre',choices=GENRE)
    date    = models.DateField(u'Publish date')

EDIT:

After closer inspection sgarza62 example seems to work bad with large amount of data. So i tried new django 1.7 feature Prefetch

authors = Author.objects.all().prefetch_related(
        Prefetch("book", queryset=Book.objects.filter(genre=0,date_from__lte=datetime.datetime.now()), to_attr='x_fantasy'),
        Prefetch("book", queryset=Book.objects.filter(genre=1,date_from__lte=datetime.datetime.now()), to_attr='x_crime'),
        Prefetch("book", queryset=Book.objects.filter(genre=2,date_from__lte=datetime.datetime.now()), to_attr='x_romance')
)

But i have 2 issues with this, how to prefetch only one object (latest book in this example) and second, how to appy ordering based on prefetched values.

Upvotes: 1

Views: 145

Answers (1)

sgarza62
sgarza62

Reputation: 6238

If you're querying all or several authors, I recommend prefetching related fields. This will snatch up all related objects in a single hit to the database, and store the objects in the Queryset.

authors = Author.objects.all().prefetch_related('book_set')
for author in authors:
    # accessing related field will not cause a hit to the db,
    # because values are cached in Queryset
    for book in author.books_set:
        print book.genre

If you're only querying one author, then it's not such a big deal.

author = Author.objects.get(pk=1)
her_books = author.book_set
for book in her_books:
    print book.genre

Edit

I'm having a bit of trouble understanding exactly what you're going to do. But, if you're looking for the latest book of each genre, for a given author:

author = Author.objects.get(pk=1)
author_books = author.book_set.order_by('-date') # most recent, first
author_genres = set([b.genre for b in author_books])
for g in author_genres:
    print next((b for b in author_books if b.genre==g), None)

Keep in mind that these operations are all on the Queryset, and are not hitting the database each time. This is good, because querying the database is an expensive operation, and most authors have a relatively small list of works, so the Querysets will generally be small.

Upvotes: 2

Related Questions