Django (PostgreSQL) query filter to exclude given letters

Question

The user sends one of the letters from the following alphabet and I want to sent appropriate response.

As you can notice there is some mixture of Cyrillic and Latin. I have the following get_queryset method:

def get_queryset(self):
        SPECIAL_CHARACTERS = [
            'Аь', 'Гl', 'Кх', 'Къ', 'Кl', 'Оь', 'Хь', 'Хl', 'Цl', 'Чl', 'Юь', 'Яь'
        ]
        queryset = WordChechenModel.objects.all().order_by('word_chechen')
        first_letter = self.request.query_params.get('letter', None)
        if first_letter is not None:
            if len(first_letter) == 2:
                queryset = queryset.filter(
                    word_chechen__iregex=r"^w+%s" % first_letter)
            else:
                queryset = queryset.filter(Q(word_chechen__startswith=first_letter) | Q(
                    word_chechen__startswith=first_letter.upper())).exclude(
                    word_chechen__startswith__in=SPECIAL_CHARACTERS)
        return queryset

In case that user sends a request for double(mixture) letter I can filter that using regex, but if a user sends request lets to say with Аь or some of the SPECIAL_CHARACTERS characters than I have problems.

Currently, I tried to exclude all words that start with one of the letters defined in SPECIAL_CHARACTERS using the following query:

queryset = queryset.filter(Q(word_chechen__startswith=first_letter) | Q(
                        word_chechen__startswith=first_letter.upper())).exclude(
                        word_chechen__startswith__in=SPECIAL_CHARACTERS)
            return queryset

But this gives me the following error:

django.core.exceptions.FieldError: Unsupported lookup 'startswith' for CharField or join on the field not permitted, perhaps you meant s
tartswith or istartswith?

I would be very grateful for the ideas. The speed of processing the request is important. Therefore, it would nice to get a review of my code at the same time.

Regex queryset filters? https://docs.djangoproject.com/en/2.1/ref/models/querysets/#regex — nigel222, May 30 '19 at 08:52

score 0 · Accepted Answer · answered May 30 '19 at 11:02

I've resolved this problem using regex, thx nigel222 for pointing on that. I found solution in this post https://stackoverflow.com/a/2667582/7986808
My working code:

def get_queryset(self):
        SPECIAL_CHARACTERS = [
            'Аь', 'Гl', 'Кх', 'Къ', 'Кl', 'Оь', 'Хь', 'Хl', 'Цl', 'Чl', 'Юь', 'Яь'
        ]
        queryset = WordChechenModel.objects.all().order_by('word_chechen')
        first_letter = self.request.query_params.get('letter', None)
        if first_letter is not None:
            if len(first_letter) == 2:
                queryset = queryset.filter(
                    word_chechen__regex=r"^%s" % first_letter)
            else:
                queryset = queryset.filter(
                    word_chechen__regex=r"^%s" % first_letter).exclude(
                        word_chechen__regex=r'(^' + '|^'.join(SPECIAL_CHARACTERS) + ')')
        return queryset

Django (PostgreSQL) query filter to exclude given letters

1 Answers1