Filter using __regex doesn't return results but printing query in DB does

Using Django Using the ORM
1

Django 3 using MySQL 8 backend

Here is my query that doesn’t return anything from Django ORM but if I copy and paste into MySQL it returns records:
SELECT control_frameworkcontrol.control_number FROM control_frameworkcontrol LEFT OUTER JOIN control_frameworkdomain ON (control_frameworkcontrol.domain_id = control_frameworkdomain.id) WHERE REGEXP_LIKE(control_frameworkcontrol.control_number, ‘([A-D])+’, ‘i’) ORDER BY control_frameworkdomain.name DESC

I have tried by iregex and regex with the same results.
Here is my code that produces that query:

individual_columns_filter.append(Q(**{COLUMNS[cnt] + “__iregex”:r"‘“+ind_search+”’"}))

then i do:
record_list = ModelClass.objects
record_list = record_list.filter(reduce(operator.and_, individual_columns_filter))

I have tried with and without having ‘r’ after __iregex": and doesn’t matter. Again, I can copy and paste record_list.query into the DB and it returns results but Django ORM is not returning results.

Thanks!

2

My first recommendation would be to manually create one query with known filters to verify that the ORM query works as expected before trying to dynamically create it.

If your created query works, then you know that it’s something in how you’re dynamically building the query that is causing the problem. If it doesn’t work, then there’s something wrong with the query itself.

Side note: Single lines of code (or a partial line with code) should be enclosed within single backtick - ` characters. Or, you can enclose a complete line between lines of three backtick characters if the code itself contains backtick characters.

3

Here is a more simplified version that I manually created.

FrameworkControl.objects.filter(control_number__iregex="'([A-D])+'").values('control_number').order_by('control_number')

This gives me 0 results. But if I do:

By doing:
FrameworkControl.objects.filter(control_number__iregex="'([A-D])+'").values('control_number').order_by('control_number').query

I get this sql query:
SELECT `control_frameworkcontrol`.`control_number` FROM `control_frameworkcontrol` WHERE REGEXP_LIKE(`control_frameworkcontrol`.`control_number`, '([A-D])+', 'i') ORDER BY `control_frameworkcontrol`.`control_number` ASC

Which if I paste that into my DB, I get the correct results back. So I am confused why the Django ORM doesn’t get results when it is setting up the query correctly.

Any thoughts would be appreciated. (I have tried this on multiple queries / ORM calls, all with the same results as above).

4

First, the output from the .query attribute is not the exact sql that Django issues to the database. It’s a representation of the current state of the query. You would need to look at connection.queries to see what was actually issued. (Most importantly, the output from the .query attribute is not guaranteed to be syntactically correct.)

Aside from that, it appears to me that you have an extra set of quotes around your regex. If you look at the examples in the iregex docs, you’ll see that the regex is a string and is not additionally quoted. This means this query should probably be:

(Now, this wouldn’t be the case if the control_number field actually contained quotes, but based upon what you’re reporting from the query, I don’t think that’s the case here.)