Django order by advanced usage

  • Recently, a special ordering requirement has emerged in the business: the contents of the recommendation pool should be ranked first, and the rest of the IDs should be in ascending order

To prepare

  • Define the model

    class TestCaseWhen(models.Model):
    name = models.CharField(max_length=255)

  • Generate the data

    from test_case_when.models import TestCaseWhen


data_list = []
for num in range(10000):
    name = 'name:{}'.format(num)
    data_list.append(TestCaseWhen(name=name))
    
TestCaseWhen.objects.bulk_create(data_list)

  • Check the data

    TestCaseWhen.objects.all().count()
Out[7]: 10000
TestCaseWhen.objects.all().values_list('id', 'name')
Out[8]: <QuerySet [(1, 'name:0'), (2, 'name:1'), (3, 'name:2'), (4, 'name:3'), (5, 'name:4'), (6, 'name:5'), (7, 'name:6'), (8, 'name:7'), (9, 'name:8'), (10, 'name:9'), (11, 'name:10'), (12, 'name:11'), (13, 'name:12'), (14, 'name:13'), (15, 'name:14'), (16, 'name:15'), (17, 'name:16'), (18, 'name:17'), (19, 'name:18'), (20, 'name:19'), '...(remaining elements truncated)...']>

demand

  • TestCaseWhen id_list [10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 1000]
  • TestcaseWhen (id, id, id, id, id, id, id, id)

Analysis of the

  • The normal.order_by(‘id’) certainly does not solve this need
  • You don’t want to convert a QuerySet to a list sort
  • Forget ORM, how do you write SQL

  • Think of the case when function in SQL

    SELECT id, name
FROM test_case_when_testcasewhen
ORDER BY CASE WHEN id = 10 THEN 0
              WHEN id = 20 THEN 1
              WHEN id = 30 THEN 2
              WHEN id = 40 THEN 3
              WHEN id = 50 THEN 4
              WHEN id = 60 THEN 5
              WHEN id = 70 THEN 6
              WHEN id = 80 THEN 7
              WHEN id = 90 THEN 8
              WHEN id = 100 THEN 9
              WHEN id = 1000 THEN 10
         ELSE 11
         END, id ASC ;
  • The database goes on a wave
  • It looks like success
  • All you need to think about now is how to use Django ORM to translate this into an SQL statement
  • Check the documentation for Django case when usage
  • But…
  • Only ask the omnipotent net friend
  • And I finally found this

  • Here’s how it applies to us

    from django.db.models import Case, When


id_list = [10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 1000]
order_rule = Case(*[When(id=id, then=pos) for pos, id in enumerate(id_list)], default=len(id_list))
qs = TestCaseWhen.objects.all().order_by(order_rule, 'id')

  • Print the generated SQL

    print(qs.query)

SELECT "test_case_when_testcasewhen"."id", "test_case_when_testcasewhen"."name" FROM "test_case_when_testcasewhen" ORDER BY CASE WHEN "test_case_when_testcasewhen"."id" = 10 THEN 0 WHEN "test_case_when_testcasewhen"."id" = 20 THEN 1 WHEN "test_case_when_testcasewhen"."id" = 30 THEN 2 WHEN "test_case_when_testcasewhen"."id" = 40 THEN 3 WHEN "test_case_when_testcasewhen"."id" = 50 THEN 4 WHEN "test_case_when_testcasewhen"."id" = 60 THEN 5 WHEN "test_case_when_testcasewhen"."id" = 70 THEN 6 WHEN "test_case_when_testcasewhen"."id" = 80 THEN 7 WHEN "test_case_when_testcasewhen"."id" = 90 THEN 8 WHEN "test_case_when_testcasewhen"."id" = 100 THEN 9 WHEN "test_case_when_testcasewhen"."id" = 1000 THEN 10 ELSE 11 END ASC, "test_case_when_testcasewhen"."id" ASC

  • View the output data

    print(qs.values_list('id', 'name'))

<QuerySet [(10, 'name:9'), (20, 'name:19'), (30, 'name:29'), (40, 'name:39'), (50, 'name:49'), (60, 'name:59'), (70, 'name:69'), (80, 'name:79'), (90, 'name:89'), (100, 'name:99'), (1000, 'name:999'), (1, 'name:0'), (2, 'name:1'), (3, 'name:2'), (4, 'name:3'), (5, 'name:4'), (6, 'name:5'), (7, 'name:6'), (8, 'name:7'), (9, 'name:8'), '...(remaining elements truncated)...']>
  • So that’s problem solved

Pay attention to

  • There may be performance issues due to different amounts of data