How to make this django model query

Question

My model looks something like this:

uri
-----
1@domainXY
2@domainYZ
3@domainZK

I want to match username parts of this uris to some list. So if there is no @domain part of the uri, I would type:

L = ['1', '2']
model.objects.filter(uri__in=L)

but how can I ignore the domain part in this query? So what I need is something like this (I understand this is not django-way how to do it, but just to explain what I'm trying to do):

L = ['1', '2']
model.objects.filter(strip_domain_part_with_at(uri)__in=L)

I cannot and do not want to make any raw queries.

Answer 1

Depending on your data you may be able to use __startswith:
https://docs.djangoproject.com/en/dev/ref/models/querysets/#startswith

However if your numeric prefixes go beyond single digits (and are not zero-padded to constant length) a simple string comparison like that isn't going to work properly. (i.e. 1, 10, 104 all 'start with' 1)

You can try a __regex query instead:
https://docs.djangoproject.com/en/dev/ref/models/querysets/#regex

eg

model.objects.filter(uri__regex==r'^(%s)@.+' % '|'.join(L))

You should profile the performance of this query on your data as use of regex may be slow (even database backends which support __regex natively probably can't use indexes for a regex query, so will do a full table scan).

Probably a better approach would be to store the part before the @ in an additional model field so you can run a simpler query on it.

Answer 2

from django... import Q

qry = " | ".join(["Q(uri__startswith ='%s@')"%v for v in L])
model.objects.filter(eval(qry))

there may be a better way to do it than with eval