Django annotate StrIndex for empty fields

user_1526119 0
Viewed 356

I am trying to use Django StrIndex to find all rows with the value a substring of a given string.

Eg: my table contains:

+----------+------------------+
|   user   |      domain      |
+----------+------------------+
| spam1    | spam.com         |
| badguy+  |                  |
|          | protonmail.com   |
| spammer  |                  |
|          | spamdomain.co.uk |
+----------+------------------+

but the query

SpamWord.objects.annotate(idx=StrIndex(models.Value('xxxx'), 'user')).filter(models.Q(idx__gt=0) | models.Q(domain='spamdomain.co.uk')).first()

matches <SpamWord: *@protonmail.com>

The query it is SELECT `spamwords`.`id`, `spamwords`.`user`, `spamwords`.`domain`, INSTR('xxxx', `spamwords`.`user`) AS `idx` FROM `spamwords` WHERE (INSTR('xxxx', `spamwords`.`user`) > 0 OR `spamwords`.`domain` = 'spamdomain.co.uk')

It should be <SpamWord: *@spamdomain.co.uk>

this is happening because INSTR('xxxx', '') => 1 (and also INSTR('xxxxasd', 'xxxx') => 1, which it is correct)

How can I write this query in order to get entry #5 (spamdomain.co.uk)?

orm django-orm django
2 Answers

The order of the parameters of StrIndex [Django-doc] is swapped. The first parameter is the haystack, the string in which you search, and the second one is the needle, the substring you are looking for.

You thus can annotate with:

from django.db.models import Q, Value

SpamWord.objects.annotate(
    idx=StrIndex('user', Value('xxxx'))
).filter(
    Q(idx__gt=0) | Q(domain='spamdomain.co.uk')
).first()
user_675790

Just filter rows where user is empty:

(~models.Q(user='') & models.Q(idx__gt=0)) | models.Q(domain='spamdomain.co.uk')
user_62503850
Related