Django ORM Annotate Count Items and Order by Specific Order?

Question

I have a graph showing different licence types by region and how many active licences there are in each region. Is there a way to order the items in a specific order in the queryset to be output to the graph?

These are my models:

class Licence(models.Model):
    status = models.CharField(choices=STATUS, max_length=1000)
    number = models.CharField(unique=True, max_length=1000)
    licence_type = models.ForeignKey(
        "LicenceType", on_delete=models.SET_NULL, null=True
    )

class LicenceType(models.Model):
    region = models.ForeignKey(
        "Region", on_delete=models.SET_NULL, null=True
    )

class Region(models.Model):
    slug = models.SlugField(primary_key=True)

Here is my view:

def dashboard(request):
    # total number of active licences across regions
    active_licences = (
        Licence.objects.values("licence_type", "licence_type__region")
        .annotate(total=Count("id"))
        .order_by("licence_type")
    )
    return render(request, "dashboard.html", "active_licences": active_licences)

Is there a way that I can specify the order in which the regions appear? For example, they are currently in the order (by pk) [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] but I want them to appear as [1, 3, 2, 4, 5, 6, 7, 8, 9, 10].

Seems you are currently ordering by `licence_type`. Can you explain a bit more about the desired order? — kamran890, Sep 21 '21 at 21:42
Right now they are just ordered by pk, but the client wants to see the regions/provinces in a specific order, so I need to be able to order them so that "ON" is first, "AB" is second, "BC" is third, etc. instead of "ON", "BC", "AB", etc. — kbdev, Sep 22 '21 at 12:37
Basically I want to prioritize the first three and then show the rest in whatever order. — kbdev, Sep 22 '21 at 12:41

score 0 · Accepted Answer · answered Oct 01 '21 at 19:10

I ended up manipulating the data from the queryset in the view and returned it to the template as a list of tuples like so:

def dashboard(request):
    # total number of active licences across provinces
    licences_by_province = list(
        Licence.objects.values("licence_type__region")
        .annotate(total=Count("id"))
        .order_by("licence_type")
    )

    # save provinces and totals to a list
    licence_list = []
    for province in licences_by_province:
        licence_list.append(tuple(province.values()))

    # re-order list to include ON, AB, BC at the beginning
    d = dict([('ON', 0), ('AB', 0), ('BC', 0)])
    d.update(dict(licence_list))
    active_licences = list(d.items())

    return render(request, "dashboard.html", "active_licences": active_licences)

BKO · Answer 2 · 2023-06-06T01:45:23.180

It seems that conditional ordering is what you are looking for.

Try:

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


your_order = ['ON', 'AB', 'BC', ... ]
Licence.objects.alias(
    order_by_region=Case(
        *[When(licence_type__region=item, then=Value(i) for i, item in enumerate(your_order))],
        default=Value(len(your_order)),
        output_field=models.IntegerField(),
    )
).values('licence_type__region').annotate(total=Count('id')).order_by('order_by_region')

See this answer.

Django ORM Annotate Count Items and Order by Specific Order?

2 Answers2