CODEWITHSUNDEEP
pythondjango
Wizard
Wizard

Reputation: 22043

Sort the queryset in an arbitrary order

I have such a template to present the blocks from database 

<div class="panel panel-default">
  <div class="panel-heading">
    <a style='font-size:18pt' href="article/list/{{ b.id }}">{{ b.name }}</a>
    <span class='pull-right'>{{ b.admin }}</span>
  </div>
  <div class="panel-body">
    {{ b.desc }}
  </div>
</div>

the data model:

class Block(models.Model):
    STATUS = (
        (1,  'normal'),
        (0, 'deleted'),
    )
    name = models.CharField("block name", max_length=100)
    desc = models.CharField("block description", max_length=100)
    admin = models.CharField("block admin", max_length=100)
    status = models.IntegerField(choices=STATUS)

    class Meta:
        ordering = ("id",)

    def __str__(self):
        return self.name

I retrieve the data as 

In [2]: from article.models import Block
In [3]: blocks = Block.objects.all()
In [4]: blocks
Out[4]: <QuerySet [<Block: Concepts>, <Block: Reading>, <Block: Coding>, <Block: Action>]>

I want to present the data as an arbitrary order of ['concept','code', 'read', 'action'] rather than by id,

By observing, I find that this could achieved by order its second letter,

In [7]: sorted(l, key=lambda item: item[1], reverse=True)
Out[7]: ['concept', 'code', 'read', 'action']

How could sort the queryset this way?

Upvotes: 2

Views: 506

Answers (2)

Arpit Singh
Arpit Singh

Reputation: 3467

Using Django's Conditional Expressions

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

array = ['concept', 'code', 'read', 'action']

Block.objects.annotate(
    rank=Case(
        *[When(name=name, then=Value(array.index(name))) for name in array],
        default=Value(len(array)),
        output_field=IntegerField(),
    ),
).order_by('rank')

Upvotes: 5

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476493

You could first annotate every Block instance, and then order by this annotation that uses the Substr function [Django-doc], like:

from django.db.models.functions import Substr

Block.objects.annotate(
    sndchar=Substr('name', 2, 1)
).order_by('-sndchar')

as extra bonus every Block instance will have a sndchar attribute which is the second character of the name attribute (only in this queryset). But that is not really a problem I think. In case it clashes with another column, you can rename it.

The minus char in -sndchar means that we will sort in descending order. You can remove it if you want to sort in ascending order.

The sorting is thus here done at the database level, which is usually (significantly) faster than doing this at the Django level.

You can also sort on the string starting from the second character (such that in case of a tie, the third character, etc. is taken into account), by dropping the length parameter, and thus annotate with the name except the first character:

from django.db.models.functions import Substr

# This will sort on the third character in case of a tie, and so on
Block.objects.annotate(
    sndchar=Substr('name', 2)
).order_by('-sndchar')

Upvotes: 3

Related Questions