Django order by 高级用法

Django order by 高级用法

• 最近业务上出现了一个比较特殊的排序需求：推荐池的内容要排在最前面，其他的 id 升序

准备

• 定义model

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

• 生成数据

  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)
  

• 检查数据

  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)...']>
  

需求

• 现在有这样一个列表 TestCaseWhen id_list [10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 1000]
• 现在要让 TestCaseWhen 的 queryset 先按照 id_list 中的 id 位置进行排序，不在 id_list 的按照 id 升序

分析

• 普通的 .order_by(‘id’) 肯定不能解决这个需求

• 又不想把 queryset 转换为列表排序

• 不谈 orm ，sql 该怎么写

• 想到了 sql 中的 case when 函数

  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 ;
  

• 数据库走一波
  

• 这样一看好像成功了

• 那现在需要思考的就是咋用 django orm 转化成这样的 sql 语句了

• 先去查查文档, django case when 用法

• but , 并没有。。。。

• 那只能去问万能的网友了

• 最终找到了这个

• 运用到我们这里是这样的

  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')
  

• 打印生成的 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
  
  

• 查看输出数据

  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)...']>
  
  

• 这样看来问题就解决了

注意

• 此处可能会因为数据量的不同而产生性能问题