How do I make and access regex capture groups in Django without RawSQL?

Question

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

Answer 1

From Django 1.8 onwards, you can use Func() expressions.

from django.db.models import Func

class EndNumeric(Func):
    function = 'REGEXP_MATCHES'
    template = "(%(function)s(%(expressions)s, '^(.*\D)([0-9]*)$'))[2]::integer"

qs = Foo.objects.annotate(
    grp2=EndNumeric('id'),
).values('id', 'grp2').order_by('grp2')

Reference: Get sorted queryset by specified field with regex in django