I'm having difficulties sorting data from a stock management database in a Django query. The data has to be ordered by 2 related fields, and this results in duplicates. I understand that this is expected behavior, but I can't seem to find an elegant way to get rid of those duplicates.
models.py:
class Attribute(models.Model):
name = models.CharField(max_length=100)
class ProductType(models.Model):
name = models.CharField(max_length=100)
attributes = models.ManyToManyField(Attribute, through='ProductTypesAttributes')
class ProductTypesAttributes(models.Model):
product_type = models.ForeignKey(ProductType, on_delete=models.CASCADE)
attribute = models.ForeignKey(Attribute, on_delete=models.CASCADE)
class AttributePossibleValues(models.Model):
attribute = models.ForeignKey(Attribute, on_delete=models.CASCADE)
name = models.CharField(max_length=100)
sorting_value = models.IntegerField(null=True)
class Meta:
ordering = ('sorting_value', )
class Product(models.Model):
product_type = models.ForeignKey(ProductType, on_delete=models.CASCADE)
attributes = models.ManyToManyField(Attribute, through='ProductAttributes')
price = models.DecimalField(max_digits=8, decimal_places=2)
class ProductAttributes(models.Model):
product = models.ForeignKey(Product, on_delete=models.CASCADE)
attribute = models.ForeignKey(Attribute, on_delete=models.CASCADE)
value = models.ForeignKey(AttributePossibleValues, on_delete=models.CASCADE, null=True)
The query which is returning duplicates (views.py):
product_type = ProductType.objects.get(pk=data['product_type_id'])
queryset = Product.objects.filter(product_type=product_type)
queryset = queryset.order_by('productattributes__attribute__name','productattributes__value__sorting_value')
For example:
A specific red sweater made out of wool, is represented by a Product instance. This instance is linked to 2 Attribute instances (color and material) through a many-to-many intermediary table. This intermediary table also links the attributes to the correct values for these attributes (red and wool).
Now I want to generate a list with the prices of all sweaters. I want this list to be sorted. I want to see the cotton sweaters first, and then the sweaters made out of wool. And those cotton and wool sweaters should also be sorted by their color, so the user sees prices of the blue sweaters before those of the red sweaters.
So I use the query above, and each sweater is shown twice. This is expected: I'm trying to order a single object with respect to two different values (material and color). But I need each sweater to be shown only once.
I've tried:
- I found similar questions (such as this one) in which the issue was fixed by annotating the queryset and then sorting by the annotated value. But I can't think of something to annotate my query with, that would solve my problem.
- I can't use
.distinct()
because I'm using a mySQL database.
I've worked around this issue by manually filtering out duplicate pks. But I'm still interested in a more elegant solution.