Using MySQL REGEXP to filter string with Django ORM

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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(<strong>media__iregex=r'^media/[^/]+$'</strong>)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0