Opened 2 years ago

Last modified 5 months ago

#29702 new New feature

QuerySet database-side pattern regexp support

Reported by: Alex Damian Owned by: nobody
Component: Database layer (models, ORM) Version: 2.1
Severity: Normal Keywords: new feature, queryset, regex
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Currently there is no API to match a string against regex patterns stored in a database. Proposing an API to enable this functionality.

Current regex API

The current set of QuerySet APIs supports regexp functions (i.e __regex and __iregex) support passing in a pattern that is matched against a field in the database. E.g. for a database value_field=CharField() containing the foo, bar and for, the queryset API Q(value__regexp='.o.') will match foo and for through a value_field REGEX 'o' WHERE clause.

New API proposal

What is missing is an API to allow the regex (proposing __revregex and __irevregex) pattern to be stored in the field and to apply a WHERE clause on a string that returns pattern-matching entries in the database.

E.G have the database field pattern=CharField() that stores a regex pattern, with the values '.o.' and '.*'.
The queryset statement Q(pattern__revregex='foo') will match both values, while the pattern Q(pattern__revregex)='bar') will match only the .* value.

Example

Currently I'm implementing this with an extra() query:

class DisplayClassifiers(models.Model):
    regex_pattern = models.CharField(max_length=10, null=False)

....

 # return my classifiers that match the string supplied

DisplayClassifiers.objects.extra(where=["'{0}' REGEXP regex_pattern".format(regex_pattern.replace("'", ""))])

This generates this SQL:

 SELECT .... FROM `mainapp_displayclassifier` WHERE ('foo-bar-go' REGEXP regex_pattern)

Works as expected with mariadb 10.0.31

Change History (5)

comment:1 Changed 2 years ago by Alex Damian

Summary: QuerySet database pattern regexp supportQuerySet database-side pattern regexp support

comment:2 Changed 2 years ago by Alex Damian

Component: UncategorizedDatabase layer (models, ORM)
Keywords: new feature queryset regex added
Type: UncategorizedNew feature

comment:3 Changed 2 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:4 Changed 2 years ago by Simon Charette

I'm not sure if this warrants inclusion as a core lookup, this seems like a rare use case and best suited for a third-party RegexField that implements this special lookup and pattern validation?

Last edited 2 years ago by Simon Charette (previous) (diff)

comment:5 Changed 5 months ago by Adam (Chainz) Johnson

This can also be done with regex database functions [like those provided by Django-MySQL](https://django-mysql.readthedocs.io/en/latest/database_functions.html#regexp-functions). Not sure there's enough similarity across DB's to warrant a cross-DB regex function, although I guess we already have the lookup.

Note: See TracTickets for help on using tickets.
Back to Top