[Django]-Django startswith on fields

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)

Bit of a mouthful but you can do this by annotating your search value and then filtering against it. All happens pretty quickly in-database.

from django.db.models import Value as V, F, CharField

Address.objects.exclude(
    postcode_prefix=''
).annotate(
    postcode=Value('12345', output_field=CharField())
).filter(
    postcode__startswith=F('postcode_prefix')
)

The exclude is only necessary if postcode_prefix can be empty. This would result in an SQL like '%', which would match every postcode.

I’m sure you could do this via a nice templated function these days too… But this is clean enough for me.

The raw SQL query that would do that you need looks something like this:

select * from postal_code_table where '1234567' like postal_code||'%'

This query will select any postal_code from your table that is a substring of ‘1234567’ and also must start from begining, ie: ‘123’, ‘1234’, etc.

Now to implement this in Django, the preferred method is using a custom look up:

from django.db.models.fields import Field
from django.db.models import Lookup

@Field.register_lookup
class LowerStartswithContainedBy(Lookup):
    '''Postgres LIKE query statement'''
    lookup_name = 'istartswithcontainedby'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return f"LOWER({rhs}) LIKE LOWER({lhs}) || '%%'", params

Now you can write a django query such as the following:

PostCode.objects.filter(code__istartswithcontainedby='1234567') 

Similarly, if you are just looking for substring and do not require the startswith condition, simply modify the return line of as_sql method to the following:

return f"LOWER({rhs}) LIKE '%%' || LOWER({lhs}) || '%%'", params

For more detailed explanation, see my git gist Django custom lookup

A possible alternative. (Have no idea how it compares to the accepted solution with a column as the second param to like, in execution time)

q=reduce(lambda a,b:a|b, [Q(postcode__startswith=postcode[:i+1]) for i in range(len(postcode))])

Thus, you generate all prefixes, and or them together…

A. If not the issue https://code.djangoproject.com/ticket/13363,
you could do this:

queryset.extra(select={'myconst': "'this superstring is myconst value'"}).filter(myconst__contains=F('myfield'))

Maybe, they will fix an issue and it can work.

B. If not the issue 16731 (sorry not providing full url, not enough rep, see another ticket above) you could filter by fields that added with ‘.annotate’, with creation of custom aggreation function, like here:
http://coder.cl/2011/09/custom-aggregates-on-django/

C. Last and successful. I have managed to do this using monkeypatching of the following:

1. django.db.models.sql.Query.query_terms
2. django.db.models.fields.Field.get_prep_lookup
3. django.db.models.fields.Field.get_db_prep_lookup
4. django.db.models.sql.where.WhereNode.make_atom

Just defined custom lookup ‘_starts’, which has reverse logic of ‘_startswith’