Using MySQL REGEXP to filter string with Django ORM

Question

I have a model which have FileField field and in this model we have millions of records.

class MyModel(models.Model):
    media = models.FileField(upload_to=my_function, db_index=True)
    ...

These media records are stored in database like;

• media/some/folder/filename.jpg
• media/filename.jpg
• media/2021091240-10328.JPG
• media/aXay-123.jpeg
• media/some/another/folder/202110-12-12.jpeg

etc. and I need to find records which are not have nested path like /some/folder/ or /some/another/folder/ with django orm __iregex lookup.

So, I tried something like;

MyModel.objects.filter(media__iregex=r"^media/[0-9a-zA-Z\-][.][a-zA-Z]")

but it does not match and I do not understand to write proper regex [mysql regexp].

How can I do filter with mysql regexp with using Django orm to get records with only have pattern like; media/filename.extension?

Answer 1

Your regex has no quantifier, and thus will pick exactly one character for the [0-Aa-zA-Z\-] character group.

You can simply filter out elements that contain at least two slashes with:

MyModel.objects.filter(media__iregex=r'^media/[^/]+$')