python - 在 Django 中使用正则表达式按指定字段获取排序的查询集

Question

我在管理器中有以下内容:

class NullIf(Func):
template = "NULLIF(%(expressions)s, '')"

class MySiteManager(models.Manager):

def get_queryset(self):
    qws = MySiteQuerySet(self.model, using=self._db).filter(
        some_id=settings.BASE_SOME_ID).annotate(
            # This is made for sorting by short labels as by numeric values
            short_label_numeric=Cast(
                NullIf(Func(
                    F('short_label'),
                    Value('^(\D+)|(\w+)'),
                    Value(''),
                    Value('g'),
                    function='regexp_replace')),
                models.BigIntegerField())
            ).order_by('short_label_numeric', 'short_label')

    for q in qws:
        print(q.short_label, end='\n')

    return qws

打印值的输出如下所示:

11010010110210310410510610710810911110111112113114115116117118119121201211221231241251261271281291313013113213313413513613713813914140141142143144145146147148149151501511521531541551561571581591617181920200摄氏度212223242526260261262263264265fs266fs267c268c269c27273c274c275c276c28292c303023132c33c3435c3637383c4个5个5015025035045055065075085095105115125135145155165175185195205215225246个60160260360460560660760860961061161261361461561661761861962062162262377017027037047057067077087097108个8018028038048058068078088098109901902S1S10S11S12S13S14S15S16S17S18S19S2S20S3S4S5S6S7S8S9

还有我的问题:如何使用输出构建查询集，例如1 2 3 3c 4 5 6 6c ... 264 265fs 266fs 267c 268c 269c ... S1 S2 S3 S4 ???有人有任何假设吗？

Answer 1

主要思想是按数字排序，然后按标签的字符部分排序，我无法重现和测试，但解决方案可能如下所示:

首先是sql:

SELECT 
  (regexp_matches(short_label, '^\d+'))[1]::numeric AS ln,
  regexp_matches(short_label, '^\D+') as ls,
  short_label
FROM YOUR_APP_TABLENAME ORDER BY 1, 2, 3;

orm中的注释:

对于我创建的第一个 sql 条件 custom Func

In [1]: from myapp.models import *

In [2]: from django.db.models import F, Func, Value
   ...: 
   ...: class StartNumeric(Func):
   ...:     function = 'REGEXP_MATCHES'
   ...:     template = "(%(function)s(%(expressions)s, '^\d+'))[1]::int"
   ...: 
   ...: qs = Ingredient.objects.annotate(
   ...:     ln=StartNumeric('short_label'),
   ...:     ls=Func('short_label', Value('^\D+'), function='regexp_matches'),
   ...:     ).values('ln').order_by('ln', 'ls', 'short_label')
   ...: 
   ...:     

In [3]: print(qs.query)
SELECT (REGEXP_MATCHES("myapp_ingredient"."short_label", '^\d+'))[1]::int AS "ln" FROM "myapp_ingredient" ORDER BY "ln" ASC, regexp_matches("myapp_ingredient"."short_label", ^\D+) ASC, "myapp_ingredient"."short_label" ASC

In [4]: data = qs.values_list('short_label', flat=True)
   ...: print(list(data))
   ...: 
   ...: 
['1', '2c', '3c', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32c', '33c', '34', '35c', '36', '37', '38', '100', '101', '102', '103', '104', '105', '106', '107', '108', '109', '110', '111', '112', '113', '114', '115', '116', '117', '118', '119', '120', '121', '122', '123', '124', '125', '126', '127', '128', '129', '130', '131', '132', '133', '134', '135', '136', '137', '138', '139', '140', '141', '142', '143', '144', '145', '146', '147', '148', '149', '150', '151', '152', '153', '154', '155', '156', '157', '158', '159', '200c', '260', '261', '262', '263', '264', '265fs', '266fs', '267c', '268c', '269c', '273c', '274c', '275c', '276c', '302', '501', '502', '503', '504', '505', '506', '507', '508', '509', '510', '511', '512', '513', '514', '515', '516', '517', '518', '519', '520', '521', '522', '524', '601', '602', '603', '604', '605', '606', '607', '608', '609', '610', '611', '612', '613', '614', '615', '616', '617', '618', '619', '620', '621', '622', '623', '701', '702', '703', '703', '704', '705', '706', '707', '708', '709', '710', '801', '802', '803', '804', '805', '806', '807', '808', '809', '810', '901', '902', 'aaaa', 'ddd', 'ddeee', 'rrrrr', 'S1', 'S10', 'S11', 'S12', 'S13', 'S14', 'S15', 'S16', 'S17', 'S18', 'S19', 'S2', 'S20', 'S3', 'S4', 'S5', 'S6', 'S7', 'S8', 'S9', 'vvvv', 'zzzz']

希望对你有帮助