CODEWITHSUNDEEP
djangodjango-rest-frameworksql-like
Francesco Meli
Francesco Meli

Reputation: 2700

Django advanced LIKE filtering

So I'm trying to find a nice way to execute an advanced filter using the LIKE statement in Django.

Let's say I have the following records in a table called elements:

id = 1, name = 'group[1].car[8]'
id = 2, name = 'group[1].car[9]'
id = 3, name = 'group[1].truck[1]'
id = 4, name = 'group[1].car[10]'
id = 4, name = 'group[1].carVendor[1]'

I would like to select all elements that look like group[x].car[y].

To query this in SQL I would do:

SELECT * FROM elements WHERE name LIKE 'group[%].car[%]'

Now, by reading the Django documentation here, I see that the only pre-built LIKE statements are the following:

  1. contains: name LIKE '%something%'
  2. startswith: name LIKE 'something%'
  3. endswith: name LIKE '%something'

So the one I need is missing:

  1. plain like: name LIKE 'group[%].car[%]'

I'm also using Django Rest Framework to write up my API endpoints and also here we find the possibility to use:

  1. contains: name__contains = something
  2. startswith: name__startswith = something
  3. endswith: name__endswith = something

So also here, the one I need is missing:

  1. plain like: name__like 'group[%].car[%]'

Of course I know I can write a raw sql query through Django using the raw() method, but I would like to use this option if no better solution comes up, because:

  1. I need to make sure my customization is safe
  2. I need to extends the customization to DRF

Can anybody think of a way to help me out with this in a way to go with the flow with both Django and Django Rest Framework?

Upvotes: 1

Views: 139

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476813

You can use a regular expression (regex) [wiki] for this, with the __iregex lookup [Django-doc]:

Elements.objects.filter(name__iregex=r'^group\[.*\].car\[.*\]$')

if between the square brackets, only digits are allowed, we can make it more specific with:

# only digits between the square brackets
Elements.objects.filter(name__iregex=r'^group\[\d*\].car\[\d*\]$')

Since some the specifications are a bit "complex" it is better to first test your regex, for example with regex101 you can look what names will be matched, and which will not.

Upvotes: 3

Related Questions