django filter empty array in prefetch_related

user_8318946 0
Viewed 150

I used prefetch_related and Prefetch to create a list of areas ( main category), needs (subcategory), and product categories (subsubcategory) for my products.

filter_needs = Area.objects.filter(Exists(Need.objects.filter(category_need=OuterRef('pk'))))

products = Area.objects.prefetch_related(Prefetch('need_area', 
           queryset=filter_needs, to_attr='need_area__category_need__product'))
           .filter(need_area__category_need__product__isnull=False).distinct()

models.py

class Area(models.Model):
    title = models.CharField(max_length=75, blank=False)
    body = models.CharField(max_length=150, default='-', blank=False)
    publish = models.DateTimeField('publish', default=timezone.now)   

class Need(models.Model):
    title = models.CharField(max_length=75, blank=False, null=False, help_text='max 75 characters')
    body = models.CharField(max_length=150, default='-', blank=False)
    publish = models.DateTimeField(default=timezone.now)
    need_area = models.ForeignKey(Area, on_delete=models.CASCADE, related_name='need_area')

class ProductCategory(models.Model):
    title = models.CharField(max_length=400, blank=False, null=False, help_text='max 400 characters')
    body = models.TextField(default='-')
    publish = models.DateTimeField('publish', default=timezone.now)
    category_area = models.ForeignKey(Area, on_delete=models.CASCADE, related_name='category_area', null=True)
    category_need = models.ForeignKey(Need, on_delete=models.CASCADE, related_name='category_need', null=True)

class Product(models.Model):
    id = models.AutoField(primary_key=True)
    title = models.CharField(max_length=400, blank=False)
    category = models.ForeignKey(ProductCategory, on_delete = models.CASCADE, blank=True, related_name='product')

Right now query renders all areas that contain the product but with all needs (even if there is no product in need). How can exclude all needs that are empty?

django-queryset django-models django python
1 Answers

Try this:

from django.db.models import Prefetch


Area.objects.prefetch_related(
    Prefetch(
        "need_area"
        queryset=Need.objects.filter(
            category_need__products__isnull=False,
            category_need__products__status=1,
        ).distinct(),
    )
).filter(
    need_area__category_need__products__isnull=False, 
    need_area__category_need__products__status=1,
).distinct()

You have to specify the same filter on the custom prefetch as with your outer filter.

user_67598440
Related