Custom size order added to db filter

Using Django
1

Hey there, I am working on a way to sort some clothing inventory from a database.

There are quite a few different/weird sizes so I figured one way to accomplish this would be to use a size ordering list. I don’t think there will be any new sizes any time soon, so once this is set it should be good to go.

A sample of the order (picking the obvious choices) coupled with a possible method:

sizeorder = [XXS, XS, S, M, ML, L, XL, XXL]
sortedsizelist = variablename.sort(key=lambda size: sizeorder.index(size))

Now, all of sizes are in the database and I currently have a queryset that has all of the item information and it is sorted by model name. I would like to also sort it by this custom size ordering.

clothing_output = Clothing.objects.order_by('name')

I would need to do at least a few things.

  1. Send the clothing size value to sortedsizelist from the database (would take the place of variablename)
  2. Properly append this to the clothing output (guessing I could just change it to

clothing_output = Clothing.objects.order_by('name', 'sortedsizelist')

I also don’t know how this would work if an article of clothing comes in only a few of the sizes in the list.

Thanks!

2

The common relational model way of doing this is to create a reference table for these sizes. Something along the lines of:

class Sizes(models.Model):
    label = models.CharField(max_length=4)
    scale = models.IntegerField() # Value for relative ordering

Now, there’s no denying that this makes some operations a little more difficult, but it provides the ability to address all your requirements along with easily allowing for additional sizes to be added - or even reorganized or reordered - without requiring any code changes.

Whether you need that degree of flexibility is a question you would need to answer.

3

Unfortunately I don’t have any access to modify the database. I am only allowed to read information out of it.
:frowning:

4

I have created the code in python for size resorting, but now just need to integrate it into Django.

I have:

size_order = ['XS','S','M','L','XL','XXL']
data = [data from database]
print(sorted(data, key=lambda x: size_order.index(x.split(':')[0])))
5

I wonder if I could use custom template tags to implement a custom sort on the size?

https://docs.djangoproject.com/en/4.0/howto/custom-template-tags/

6

If you can’t modify the DB, you could use an annotate with a big case/when/else annotation, then order on that new field.

from django.db.models import Case, Value, When

size_annotation = Case(
    When(size='XXS', then=Value(1)),
    When(size='XS', then=Value(2)),
    ...
    When(size='XXL', then=Value(8)),
    default=Value(0) # or a high value. Depends on what you want to do when the size doesn't match.
)

clothing_output = Clothing.objects.annotate(
    sorted_size=size_annotation,
).order_by('name', 'sorted_size')

Edit:
If you have large datasets, you should test the performance of this. And if it’s poor, then consider getting access to the database to make the changes that Ken suggested because that’s probably the best solution.