How to translate icontains in django to SQL statement ?

Question

I want to get the top 5 of selling price of each month in each year.

So I put in the code like this

def query(request):
    from django.db import connection
    cursor = connection.cursor()
    cursor.execute("SELECT product_style_id ,sum(price) As total_p ,sum(cast( amount as int )) AS total_a FROM jewelry_productorder  group by product_style_id ORDER BY total_p DESC LIMIT 5 " )
    output = cursor.fetchall()
    variables = RequestContext (request, {'output':output,})
    return render_to_response('top5.html', variables)

the result came out showing the top 5 of the whole table instead of top 5 of each month in each year.

So I put in the code like this ( by adding the WHERE clause)

def query(request):
    m = request.GET['month']
    y = request.GET['year']
    d = str(y+'-'+m)
    from django.db import connection
    cursor = connection.cursor()
    cursor.execute("SELECT product_style_id ,sum(price) As total_p ,sum(cast( amount as int )) AS total_a FROM jewelry_productorder WHERE due_date LIKE %s group by product_style_id ORDER BY total_p DESC LIMIT 5 " ,[d])
    output = cursor.fetchall()
    variables = RequestContext (request, {'output':output,})
    return render_to_response('top5.html', variables)

the result came out like this

ProgrammingError at /query

operator does not exist: date ~~ unknown LINE 1: ... total_a FROM jewelry_productorder WHERE due_date LIKE E'200... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Help me please, what should I do ??

Answer 1

__icontains in Django can be written in SQL using ILIKE.

Example:

SELECT ... WHERE some_column ILIKE '%some_string%'

So you can rewrite your query like:

cursor.execute("SELECT product_style_id ,sum(price) As total_p ,sum(cast( amount as int )) AS total_a FROM jewelry_productorder WHERE due_date ILIKE %s group by product_style_id ORDER BY total_p DESC LIMIT 5", ["%%%s%%" % d])

If your DB doesn't support ILIKE (as pointed out in a comment to my answer), do something like this:

SELECT ... WHERE LOWER(some_column) LIKE LOWER('%some_string%')

Note that using LOWER, might prevent your DB from using the index (if you have an index on that column).

Also, note that Django 1.1 added aggregation support, so you should be able to make GROUP BY queries without resorting to raw SQL. Check this: http://docs.djangoproject.com/en/dev/topics/db/aggregation/