Is there a Django equivalent for writing a SQL SELECT statement with a function in the WHERE clause?

user_7903938 0
Viewed 48

I have a series of protein sequence alignments with inserts ('-' character) that need to be removed so I can search for a pattern in the sequence.

I can do this with the following SQL query:

select sequence
from sequences
where replace(sequences.sequence,"-","") like "%"+pattern+"%"

  1. As I understand it, having the replacement and pattern matching in a query should be more efficient than looping through every sequence and doing the replacement and pattern matching in python. Is this correct?

  2. If #1 is correct, is there a Django equivalent of that query?

python-3.x sqlite mysql django
1 Answers

Yes, you can make use of a Replace expression [Django-doc] with:

from django.db.models import Value
from django.db.models.functions import Replace

Sequence.objects.annotate(
    replaced=Replace('sequence', Value('-'), Value(''))
).filter(
    replaced__icontains=pattern
)

It will indeed be more efficient than fetching all records and doing the filtering at the Django/Python level, but still this will result in linear search time, which is not very efficient.

It might be better if you can somehow "prepare" the pattern with hypens, and thus remove the Replace part of the database.

user_675790
Related