Django Query ForeignKeys to return a list “grouped by”

Something is tricking my mind here.
My mission is to get a list of Categories vs Brands, querying over Products models.

Say we got something like this:

class Category(models.Model):
name = models.CharField(max_length=100) #examples: TVs, Monitors, HomeTheaters, Fridges

class Brand(models.Model):
name = models.CharField(max_length=100) #examples: Sony, LG, Apple

class Product(models.Model):
name = models.CharField(db_index=True, max_length=100)
category = models.ForeignKey(Category, on_delete=models.CASCADE)
brand = models.ForeignKey(Brand, on_delete=models.CASCADE)


What I am looking for is a query that could return me something like this:
TVs: [Sony, LG, Apple], Monitors:[Sony, Apple]
Of course it could return a queryset that I could transform into a dict later.

Since I couldn't get this done in a clean way, I did this:

categories = Category.objects.all()
products = Product.objects.select_related('category', 'brand').all()

categories_brands =
for category in categories:
categories_brands[category.name] =
for product in products:
if product.brand.name not in categories_brands[product.category.name]:
category_brands[product.category.name].append(product.brand.name)


It does work. But feels silly iterating like that. I tried "annotate" and "regroup" solutions in templatetags, but I couldn't make it work.

In addition to this question... would you have one Model class for all the products or best would be have Fridge class, TV's class and so on. I am using tags to add content like volume of a fridge and size of a monitor to filter it later. Maybe you experts can give me some advise on this as well.

Thanks in advance.

Felipe.

2 Answers
2

We can perform this in a single query, and use itertools.groupby [doc] for this:

itertools.groupby

from django.db.models import F
from itertools import groupby
from operator import itemgetter

qs = Product.objects.annotate(
category_name=F('category__name')
brand_name=F('brand__name')
).values('category_name', 'brand_name').order_by('category_name').distinct()

result =
k: [v['brand_name'] for v in vs]
for k, vs in groupby(qs, itemgetter('category_name'))


We thus first query the database for categorys and brands, and we obtain a QuerySet that contains a 'category_name' key, and a 'brand_name' key, for your sample data it would look like:

category

brand

QuerySet

'category_name'

'brand_name'

<QuerySet [
'category_name': 'Monitors', 'brand_name': 'Sony',
'category_name': 'Monitors', 'brand_name': 'Apple',
'category_name': 'TVs', 'brand_name': 'Sony',
'category_name': 'TVs', 'brand_name': 'Apple',
'category_name': 'TVs', 'brand_name': 'LG'
]>


Then we can perform a single pass over the queryset, and populate the dictionary.

Something like this ...

query = Product.objects.select_related('category', 'brand').all().query
query.group_by = ['category']
results = QuerySet(query=query, model=Product)


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.