Django filter with replace

Question

Let's say I have the following model which have a method variants():

class Example(models.Model):
    text = models.CharField(max_length=255)

    def variants(self):
        return Example.objects.filter(text=remove('xy', self.text))

The idea is to get all objects where texts are the same after certain characters are removed from the text. For example, if self.text is 'axxyy', it should match with the object which have text 'a'. Function remove() doesn't touch to the database, it returns a new string which have given characters removed. This works fine.

However, I have a need to perform the same operation on both sides of the comparison, so that variants() would behave like following:

    def variants(self):
        return Example.objects.filter(remove('xy', text)=remove('xy', self.text))

In that case, if self.txt is 'axxyy' it should match with 'a', 'ax, 'axx', 'xayy', etc. but shouldn't match with 'aa' for example, since 'a' != 'aa' after removals. Once again, I don't want to remove 'xy' from the database, only for the comparison.

I could do this with Python, but I'm wondering if there is a way to do this on database level? I've been reading docs about Func() expressions, for example Replace, but haven't found a solution yet.

Answer 1

Annotate using django's Replace function, then filter on that annotation.

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

    def variants(self):
        return Example.objects.annotate(
            removed_x=Replace('text', Value('x'), Value('')),
            removed_xy=Replace('removed_x', Value('y'), Value('')),
        ).filter(
            removed_xy=self.text.replace('x' , '').replace('y', '')
        )

Note that the replacement argument Value('') is optional, since that's actually Replace()'s default, but it's better explicit in the example.

This doesn't scale well as the number of characters grows, but there may be better solutions using regex if your DB supports that (Postgres)