Select IN with same order with Django?

I would like to select items in my table in a custom order, with SQL, if you do not specify an order, ascending primary key is used so the 'IN' order is lost. I read i开发者_如何学运维n SO that one can use this SQL request to keep the 'IN' order :

SELECT * FROM table WHERE id IN (118,17,113,23,72) ORDER BY FIELD(id,118,17,113,23,72)

Now, how can I do to code this request within django ? NOTE : I do not want to use .in_bulk() queryset method because I want SQL to do the job.

You can do this using the raw() manager method in Django 1.2+.

ids = "118,17,113,23,72"
results = MyModel.objects.raw('SELECT * FROM myapp_mymodel WHERE `id` IN (%s) '
                              'ORDER BY FIELD (id, %s)' % (ids, ids))

A few notes:

• This is potentially vulnerable to SQL injection attacks, because you can't use the parameter handling for the ORDER BY element. Be sure to sanitise the ids list.

• raw() doesn't cache queries like a normal queryset - it will re-evaluate the query every time you iterate it. If you need the values more than once, call list() on it first.

• ORDER BY FIELD is a MySQL-specific extension.

• Also note that your final request doesn't make sense: in_bulk() does use SQL to do the job. However, it isn't suitable for your query anyway.

I've tried this:

from django.db.models import Q
# first you get the IDs
ids = [1,2,3,4,5]
# after that you can get a list of Q objects
q_objects = [Q(id=id) for id in ids]
# then you need to combine all the Q objects
# I'll use a functional approach, an operation called fold_left but you can use a for instead

# i need the first element
first_q = q_objects.pop()
final_q = reduce(lambda total,q: total | q,q_objects,first_q)
# now you can get the result
MyModel.objects.filter(q)
#if you need an order simply append order_by
MyModel.objects.filter(q).order_by(something)

Anyway, take a look at the query performed by in_bulk, becouse it does use the IN SQL clause. This is a test with django 1.3 and MySQL:

python manage.py shell


>>> from testapp.models import Car
>>> from django.db import connection
>>> cars = Car.objects.in_bulk([1,2])
>>> my_queries = connection.queries
>>> my_queries[0]['sql']

This is the SQL result:

SELECT [fields]
FROM [my_table] WHERE [my_model].`id` IN (1, 2)

Of course you could only use django_toolbar.