CODEWITHSUNDEEP
djangopython-3.xdjango-modelsdjango-queryset
alex
alex

Reputation: 2541

Django: Efficient way of obtaining a number of items based on foreign key

Here's an example:

Models.py

class Category(models.Model):
    name = models.Charfield(max_length=120 blank=True, null=True, default=None)

class Product(models.Model):
    category = models.Foreignkey(Category)

This is what i made previously:

products = [Product.objects.filter(category_id=i.id)[:6] for i in Category.objects.all()]

How can i efficiently obtain a list containing 6 products per category without using 'for'?

Upvotes: 3

Views: 78

Answers (1)

Davit Tovmasyan
Davit Tovmasyan

Reputation: 3588

Postgres has a really nice way of making an array from a subquery. So if you use Postgres this will work for you.

from django.db.models import OuterRef, Subquery

class Array(Subquery):
    template = 'ARRAY(%(subquery)s)'

product_sub = Product.objects.filter(category=OuterRef('pk'))

products = Category.objects.annotate(
    product_list=Array(product_sub.values('name')[:6])
).values_list('product_list', flat=True)

This way is more efficient: Let's compare with your version

Your version: 1 query for categories + count of categories multiplied by 1 query for products. One for loop.

My version: 1 query.

Upvotes: 2

Related Questions