How to make complex contains queries in Django?

Question

Hi, I need to make query like this:

WHERE Comment like '%ev% 3628%' or Comment like '%ew% 3628%'

the number '3628' is a parametr. So I've tried in my view:

First try:

wherestr = "Comment like '%%ev%% %s%%' or Comment like '%%ew%% %s%%'" % (rev_number, rev_number)  
comment_o = Issuecomments.objects.extra(where=[wherestr])

but I've got:
TypeError at /comments_by_rev/3628/

not enough arguments for format string

Request Method: GET Request URL: http://127.0.0.1:8001/comments%5Fby%5Frev/3628/ Exception Type: TypeError Exception Value:

not enough arguments for format string

Second try:

comment = IssuetrackerIssuecomments.objects.filter(Q(comment__contains=rev_number), Q(comment__contains='ew') | Q(comment__contains='ev'))

but its not excactly the same.
Have you people of wisdom any idea how to accomplish this?

Answer 1

You need something similar to this:

from django.db.models import Q

def myview(request):
   query = "hi" #string to search for
   items = self.filter(Q(comment__contains=query) | Q(comment__contains=query))
   ...

Just make sure the query string is properly escaped.

Answer 2

Take a look at http://docs.djangoproject.com/en/dev/ref/models/querysets/, specifically

icontains: Case-insensitive containment test.

Example: Entry.objects.get(headline__icontains='Lennon')

SQL equivalent: SELECT ... WHERE headline ILIKE '%Lennon%';

Since you're looking for a pattern like %%ev%% or %%ew%%, consider the IREGEX or REGEX versions as well?

Lastly, consider performing the search differently...perhaps parse out the interesting parts of the message and put them in their own indexed columns for querying later. You'll regret doing this search once the table gets large:).

Answer 3

You almost got it right... The problem is that your % are being subsituted twice. Django actually has a way of passing parameters in the extra clause like this

wherestr = "Comment like '%%ev%% %s%%' or Comment like '%%ew%% %s%%'"
params = (rev_number, rev_number)
comment_o = Issuecomments.objects.extra(where=[wherestr], params=[params])

This is a better way of passing the parameters as it won't leave you open to SQL injection attacks like your way will.