在没有RawSQL的情况下，如何在Django中创建和访问正则表达式捕获组? [英] How do I make and access regex capture groups in Django without RawSQL?

问题描述

如何在不使用RawSQL的情况下使用Regex捕获组注释Django查询集，以便以后可以使用该值进行过滤和排序?

例如，在PostgreSQL中，我可以进行以下查询:

  CREATE TABLE foo(id varchar(100));插入foo(id)值('disk1')，('disk10')，('disk2');选择"foo"."id"，CAST((regexp_matches("foo"."id"，'^(.* \ D)([0-9] *)$'))[2] AS整数)as grp2来自"foo"ORDER BY"grp2" 

Github要点，具有更好的源代码格式 https://gist.github.com/phpdude/50675114aaed953b820e5559f8d22166

How do I annotate a Django queryset with a Regex capture group without using RawSQL so that I later can use that value for filtering and sorting?

For example, in PostgreSQL I could make the following query:

CREATE TABLE foo (id varchar(100));

INSERT INTO foo (id) VALUES ('disk1'), ('disk10'), ('disk2');

SELECT
    "foo"."id",
    CAST((regexp_matches("foo"."id", '^(.*\D)([0-9]*)$'))[2] AS integer) as grp2
FROM "foo"
ORDER BY "grp2"

dbfiddle

解决方案

You can use a custom Func class created to get it working, but I would like to implement in a better way, just like a normal function which could be used for further processing using other functions or annotations or etc. Like a "block" in the Django ORM ecosystem.

I would like to start with an "beta version" of the class which looks like this one:

from django.db.models.expressions import Func, Value

class RegexpMatches(Func):
    function = 'REGEXP_MATCHES'

    def __init__(self, source, regexp, flags=None, group=None, output_field=None, **extra):
        template = '%(function)s(%(expressions)s)'

        if group:
            if not hasattr(regexp, 'resolve_expression'):
                regexp = Value(regexp)

            template = '({})[{}]'.format(template, str(group))

        expressions = (source, regexp)
        if flags:
            if not hasattr(flags, 'resolve_expression'):
                flags = Value(flags)

            expressions += (flags,)

        self.template = template

        super().__init__(*expressions, output_field=output_field, **extra)

and a fully working example for an admin interface:

from django.contrib.admin import ModelAdmin, register
from django.db.models import IntegerField
from django.db.models.functions import Cast
from django.db.models.expressions import Func, Value

from .models import Foo


class RegexpMatches(Func):
    function = 'REGEXP_MATCHES'

    def __init__(self, source, regexp, flags=None, group=None, output_field=None, **extra):
        template = '%(function)s(%(expressions)s)'

        if group:
            if not hasattr(regexp, 'resolve_expression'):
                regexp = Value(regexp)

            template = '({})[{}]'.format(template, str(group))

        expressions = (source, regexp)
        if flags:
            if not hasattr(flags, 'resolve_expression'):
                flags = Value(flags)

            expressions += (flags,)

        self.template = template

        super().__init__(*expressions, output_field=output_field, **extra)


@register(Foo)
class Foo(ModelAdmin):
    list_display = ['id', 'required_field', 'required_field_string']

    def get_queryset(self, request):
        qs = super().get_queryset(request)

        return qs.annotate(
            required_field=Cast(RegexpMatches('id', r'^(.*\D)([0-9]*)$', group=2), output_field=IntegerField()),
            required_field_string=RegexpMatches('id', r'^(.*\D)([0-9]*)$', group=2)
        )

    def required_field(self, obj):
        return obj.required_field

    def required_field_string(self, obj):
        return obj.required_field_string

As you see in I've added 2 annotations and one outputs like a number and the other one like a normal string (character), of course, we don't see it in the admin interface but it does in the SQL are executed:

SELECT "test_foo"."id" AS Col1,
               ((REGEXP_MATCHES("test_foo"."id", '^(.*\D)([0-9]*)$'))[2])::integer AS "required_field", (REGEXP_MATCHES("test_foo"."id", '^(.*\D)([0-9]*)$'))[2] AS "required_field_string"
          FROM "test_foo"

And also a screenshot with an example for you :)

Github gist with a better source code formatting https://gist.github.com/phpdude/50675114aaed953b820e5559f8d22166

这篇关于在没有RawSQL的情况下，如何在Django中创建和访问正则表达式捕获组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！