Django Order By Conditional Expressions

Playing around with conditional ordering in Django’s ORM.

from pygments import highlight
from pygments.lexers import PythonLexer, SqlLexer
from pygments.formatters import HtmlFormatter
import IPython


def display_code(content, lexer=PythonLexer()):
    formatter = HtmlFormatter()

    return IPython.display.HTML(
        '<style type="text/css">{}</style>{}'.format(
            formatter.get_style_defs(".highlight"),
            highlight(content, lexer, formatter),
        )
    )


def display_code_file(file_path):
    with open(file_path) as fobj:
        content = fobj.read()

    return display_code(content)

# Setup Jupyter and Django.
import os
import sys
from pathlib import Path

sys.path.append(Path.cwd())
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "demo_project.settings")

import django

django.setup()

from core.models import Prospect, HotList

/usr/local/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)

display_code_file("core/models.py")

from django.contrib.postgres.fields import ArrayField
from django.db import models


class Prospect(models.Model):
    name = models.CharField('name', max_length=100)

    class Meta:
        db_table = 'prospects'
        ordering = ('name',)

    def __str__(self):
        return self.name


class HotList(models.Model):
    prospect_list = ArrayField(models.IntegerField(), size=10)

    class Meta:
        db_table = 'host_lists'

# Let's clean the database.
Prospect.objects.all().delete()
HotList.objects.all().delete()

# Got a list of Fantasy Bank Names here: <http://www.fantasynamegenerators.com/bank-names.php>
company_names = [
    "One Nation Credit Union",
    "Joint Banks",
    "Grand Summit Corporation",
    "Golden Gates Corporation",
    "Core Bancshares",
    "Lifespark Bank",
    "New Blossom Financial Holdings",
    "Boon Financial Corp.",
    "Life Essence Corporation",
    "Federal Financial Inc.",
]

for name in company_names:
    Prospect.objects.create(name=name)

Prospect.objects.all()

<QuerySet [<Prospect: Boon Financial Corp.>, <Prospect: Core Bancshares>, <Prospect: Federal Financial Inc.>, <Prospect: Golden Gates Corporation>, <Prospect: Grand Summit Corporation>, <Prospect: Joint Banks>, <Prospect: Life Essence Corporation>, <Prospect: Lifespark Bank>, <Prospect: New Blossom Financial Holdings>, <Prospect: One Nation Credit Union>]>

five_random_companies = list(
    Prospect.objects.order_by("?")[:2].values_list("pk", flat=True)
)

five_random_companies

[117, 118]

hot_list = HotList.objects.create(prospect_list=five_random_companies)

hot_list.prospect_list

[117, 118]

Prospect.objects.filter(pk__in=hot_list.prospect_list)

<QuerySet [<Prospect: Boon Financial Corp.>, <Prospect: New Blossom Financial Holdings>]>

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

prospect_list = Prospect.objects.annotate(
    is_hot=Case(
        When(pk__in=hot_list.prospect_list, then=Value(True)),
        default=False,
        output_field=BooleanField(),
    )
).order_by("-is_hot", "name")

for prospect in prospect_list:
    if prospect.is_hot:
        print("★\t{}".format(prospect))
    else:
        print("☆\t{}".format(prospect))

★   Boon Financial Corp.
★   New Blossom Financial Holdings
☆   Core Bancshares
☆   Federal Financial Inc.
☆   Golden Gates Corporation
☆   Grand Summit Corporation
☆   Joint Banks
☆   Life Essence Corporation
☆   Lifespark Bank
☆   One Nation Credit Union

import sqlparse

display_code(
    sqlparse.format(
        str(prospect_list.query), reindent=True, keyword_case="upper"
    ),
    SqlLexer(),
)

SELECT "prospects"."id",
       "prospects"."name",
       CASE
           WHEN "prospects"."id" IN (117,
                                     118) THEN TRUE
           ELSE FALSE
       END AS "is_hot"
FROM "prospects"
ORDER BY "is_hot" DESC,
         "prospects"."name" ASC

Made by Myles Braithwaite with ❤️ in Toronto.