Django ORM doesnt calls Postgres "regexp_matches" with the "g" flag

Using Django Using the ORM
1

Hi,
I want to implement a alphanumeric sorting on a django orm queryset.
The closest that I reached is:

sql = ordModel.objects.annotate(
        num=Cast(
            Func(
                F('hostname'),
                Value("\d+"),
                function='regexp_matches',
            ),
            output_field=ArrayField(IntegerField())
        ),
        char=Func(
            F('hostname'),
            Value("\D+"),
            function='regexp_matches',
            output_field=ArrayField(TextField())
        )
    ).order_by('char', 'num', ).values('hostname')

But its founding only the first match, because django is not sending the “g” flag, which Enables global matching (multiple matches).

How can I send it with the “g” flag?
Or, if I cant, Can you recommend a way to do an alphanumeric sort? (Django & Databases not support this by default)

Thanks!

2

How can I send it with the “g” flag?

Func accepts any number of arguments; simply pass 'g' as a second one to adhere to the documented signature. In other words, the same way you send the pattern.

class RegExpMatches(Func):
    function = "regexp_matches"
    arity = 3
    output_field = ArrayField(TextField())

RegExpMatches(
    "hostname",
    Value("\D+"),
    Value("g"),
)

Can you recommend a way to do an alphanumeric sort? (Django & Databases not support this by default)

I think you are mistaken and I suggest you spend some time learning about collations. For example, here’s how you can create one that defines semver ordering using SQL and here are utilities Django provides to interact with them

which you could then use to simply do

Model.objects.order_by(Collate("hostname", "you-collation-name"))
3

If I understand correctly, from the quote "If you need to filter or order a column using a particular collation that your operating system provides but PostgreSQL does not, you can manage collations in your database using a migration file. " - does it means that the whole functionality will be dependant on the OS?
If the OS is replaced, it can damage the functionality?

4

does it means that the whole functionality will be dependant on the OS?
If the OS is replaced, it can damage the functionality?

No. Please refer to the Postgres collation documentation linked above and this complementary ICU one.

What it means is that while Postgres provides default collations that are OS dependent you can use CREATE COLLATION to create your own that are bound to your database.