Django query : Request with startswith in an array

Question

here is my code :

q =  [
    "78",
    "95",
    "77",
    "91",
    "92",
    "93",
    "94",
    "75",
    "27",
    "28",
    "45",
    "89",
    "10",
    "51",
    "02",
    "60",
    "27",
]
query = reduce(operator.and_, (Q(code_postal__startswith=item) for item in q))
result = Record14.objects.filter(query)
for r in result :
print(r)

I want a query with all the objects from Record14 where the code_postal begin with the values in the q array.

I have the data in my database i'am sure, but the query is empty ...

I don't understand why.

Answer 1

The main problem here is that you use and_ as reduce operator, so that means that you specify as condition that the code_postal should start with 78 and 95 at the same time. No text/number can start with 78 and 95 (and all other values) at the same time.

You can easily fix this by reducing this with or_:

from functools import reduce
from operator import or_

query = reduce(or_, (Q(code_postal__startswith=item) for item in q))
result = Record14.objects.filter(query)

That being said, it is probably better to use a regular expression [wiki] here, like:

from re import escape as reescape

result = Record14.objects.filter(
    code_postal__regex= '^({})'.format('|'.join(map(reescape, q)))
)

For your given list q, this will result in a regex:

^(78|95|77|91|92|93|94|75|27|28|45|89|10|51|02|60|27)

The ^ is the start anchor here, and the pipe acts as a "union", so this regex looks for columns that start with 78, 95, 77, etc.