How to sort values of a query in Django based on the quantity of child table?

Question

I have a Product class that has a child class ProductColors.

These are my two classes:

class Product(models.Model):
    category = models.ForeignKey(Category, related_name='products', on_delete=models.CASCADE)
    name = models.CharField(max_length=255)
    slug = models.SlugField(unique=True, allow_unicode=True)
    description = models.TextField()
    price = models.PositiveIntegerField()
    available = models.BooleanField(default=True)
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)
    discount = models.PositiveIntegerField(default=0)
    offer_end = models.DateTimeField(blank=True, null=True)
    user_likes = models.ManyToManyField(User, related_name="product_likes", blank=True)


class ProductColor(models.Model):
    class ColorChoices(models.TextChoices):
        RED = "#f50b0b","قرمز"
        BLUE = "#0844f3","آبی"
        YELLOW ="#f7f704", "زرد"
        GREEN = "#22f704","سبز"
        PINK = "#f704e8","صورتی"
        PURPLE = "#901f89","بنفش"
        GRAY = "#9c939c","خاکستری"
        WHITE = "#ffffff","سفید"
        BLACK = "#000000","سیاه"
        ORANGE = "#f2780c","نارنجی"
        BROWN =  "#513924","قهوه ای"
        GLASS = "#f3f3f2", "بی رنگ"

    product = models.ForeignKey(Product, related_name='colors', on_delete=models.CASCADE)
    color = models.CharField(choices=ColorChoices.choices, max_length=255)
    quantity = models.PositiveIntegerField(default=0)

    def __str__(self):
        return f"{self.product.name} - {self.color}"

I am trying to sort products based on first - available and then the quantity of colors from higher quantity to zero.

I try to do this with this method :

from django.db.models import Count, When, Case, IntegerField


products = Product.objects.all().order_by('-available', 'name')
products = products.annotate(
        has_zero_quantity=Case(
            When(colors__quantity=0, then=1),
            When(colors__quantity__gt=0, then=0),
            output_field=IntegerField(),
        )
    )

but I am stuck and I can't find the correct way - can anyone help me to find a solution?

Pourya Mohamadi · Accepted Answer · 2024-10-25 20:10:33Z

-1

i find the answer and i want to share with some pepole maybe stuck like me :

from django.db.models import Prefetch
products = products.filter(available=True).prefetch_related(
        Prefetch('colors', queryset=ProductColor.objects.order_by('-quantity'))
    )

fix the problem

answered Oct 25, 2024 at 20:10

Pourya Mohamadi

2623 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to sort values of a query in Django based on the quantity of child table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related