Django startswith on fields

Question

let's say that I have an Address model with a postcode field. I can lookup addresses with postcode starting with "123" with this line:

Address.objects.filter(postcode__startswith="123") 

Now, I need to do this search the "other way around". I have an Address model with a postcode_prefix field, and I need to retrieve all the addresses for which postcode_prefix is a prefix of a given code, like "12345". So if in my db I had 2 addresses with postcode_prefix = "123" and "234", only the first one would be returned.

Something like:

Address.objects.filter("12345".startswith(postcode_prefix))  

The problem is that this doesn't work. The only solution I can come up with is to perform a filter on the first char, like:

Address.objects.filter(postcode_prefix__startswith="12345"[0]) 

and then, when I get the results, make a list comprehension that filters them properly, like this:

results = [r for r in results if "12345".startswith(r.postcode_prefix)] 

Is there a better way to do it in django?

Answer 1

Edit: This does not answer the original question but how to word a query the other way around.

I think what you are trying to do with your "something like" line is properly written as this:

Address.objects.filter(postcode__startswith=postcode_prefix) 

Answer 2

In SQL terms, what you want to achieve reads like ('12345' is the postcode you are searching for):

SELECT * FROM address WHERE '12345' LIKE postcode_prefix||'%' 

This is not really a standard query and I do not see any possibility to achieve this in Django using only get()/filter().

However, Django offers a way to provide additional SQL clauses with extra():

postcode = '12345' Address.objects.extra(where=["%s LIKE postcode_prefix||'%%'"], params=[postcode]) 

Please see the Django documentation on extra() for further reference. Also note that the extra contains pure SQL, so you need to make sure that the clause is valid for your database.

Hope this works for you.