CODEWITHSUNDEEP
djangodjango-orm
Danial Tz
Danial Tz

Reputation: 1984

How to get number of items grouped by a property of an intermediate model

I would like to have something like this

Adventure (4) | Sci-fi (12)

which are the books, in a bookshop, linked by a local price.

Say, Hobbit is $5 at Amazon and $6 at Barnes. So if I was listing the books in Amazon I will have Adventure (1) as the count of the books with a specified price in amazon.

If I do like this I get the correct Genres:

for u in Bookshop.objects.get(pk=1).BookBookshopLink_set.all():
    print u.book.genre

which would print, e.g.:

Sci-fi
Sci-fi
Adventure

Here are the models:

from parler.models import TranslatableModel, TranslatedFields
from parler.managers import TranslationManager

class Genre(TranslatableModel):
    translations = TranslatedFields(
        name=models.CharField(max_length=200),
        slug=models.SlugField(),
        description=models.TextField(blank=True),
        meta={'unique_together': [('language_code', 'slug')]},
    )

    published = models.BooleanField(default=False)


class Book(TranslatableModel):
    translations = TranslatedFields(
        name=models.CharField(max_length=200),
        slug=models.SlugField(),
        description=models.TextField(blank=True),
        meta={'unique_together': [('language_code', 'slug')]},
    )

    genre = models.ForeignKey(Genre, blank=True, null=True)
    published = models.BooleanField(default=False)


class Bookshop(TranslatableModel):
    translations = TranslatedFields(
        name=models.CharField(max_length=200),
        description=models.TextField(_('Description'), default='', blank=True),
        slug=models.SlugField(),
        meta={'unique_together': [('slug', 'language_code')]},
    )

    booklist = models.ManyToManyField(Book, blank=True, through='BookBookshopLink')


class BookBookshopLink(TranslatableModel):
    bookshop = models.ForeignKey(Bookshop)
    book = models.ForeignKey(Book)
    price = models.IntegerField(blank=True, null=True)

Upvotes: 0

Views: 95

Answers (2)

AlvaroAV
AlvaroAV

Reputation: 10563

To do what you're trying to achieve in one query, you need to use Count, annotate and values_list

I'll show you a code example and then I'll try to explain it:

from django.db.models import Count
from your_project.models import *

Genre.objects.all().values_list('name').annotate(num_books=Count('book'))
  1. .values_list('name'): This return a list of all genres by name
  2. .annotate(num_books=Count('book')): This count books for each Genre

I have a similar models structure in my projects and when I execute that code, I get this as answer:

[(u'GENRE_NAME', 13), (u'GENRE_NAME', 14), (u'GENRE_NAME', 0),...]

You can parse the output of this query to fit your expectations

I also recomend you to check oficial documentation Django Agreggation

Upvotes: 1

Persijn
Persijn

Reputation: 14990

This loops over all your genres and prints out how many of them there are. Even if there are 0 in a genre.

for a_genre in Gendre.objects.all():
    print Book.objects.filter(genre=a_genre).count()

and if you want it printed with the genre aswel

for a_genre in Gendre.objects.all():
    print "%s (%d)" % (a_genre, Book.objects.filter(genre=a_genre).count())

Documentation for filters in django : https://docs.djangoproject.com/en/1.7/topics/db/queries/#retrieving-specific-objects-with-filters

Upvotes: 0

Related Questions