3

I have a list of pk's and I would like to get the result in the same order that my list is defined... But the order of the elements is begging changed. How any one help me? 

print list_ids

[31189, 31191, 31327, 31406, 31352, 31395, 31309, 30071, 31434, 31435]

obj_opor=Opor.objects.in_bulk(list_ids).values()

for o in obj_oportunidades:
   print o

31395 31435 31434 30071 31309 31406 31189 31191 31352 31327

This object should be used in template to show some results to the user... But how you can see, the order is different from the original list_ids

Thomas
  • 2,256
  • 6
  • 32
  • 47
  • 2
    That's because queries don't care about the order of the search criteria. – Ignacio Vazquez-Abrams Sep 01 '12 at 15:11
  • I'm not sure this fits in your requirements, but you may try sorting the list first (`list_ids.sort()`) before querying. – Rohan Sep 01 '12 at 16:43
  • Hello Rohan, actually the list_ids is already in correct order. The problem is the return of "in_bulk", it should keep the same elements order. – Thomas Sep 01 '12 at 17:59
  • Possible duplicate of [Django get a QuerySet from array of id's in specific order](http://stackoverflow.com/questions/4916851/django-get-a-queryset-from-array-of-ids-in-specific-order) – Vanni Totaro Jan 22 '16 at 17:44
  • Please refer to this answer: https://stackoverflow.com/questions/7361243/django-in-query-lookup-doesnt-maintain-the-order-in-queryset/70830205#70830205 – Rashid Mahmood Jan 24 '22 at 07:25

3 Answers3

1

Would have been nice to have this feature in SQL - sorting by a known list of values.

Instead, what you could do is:

obj_oportunidades=Opor.objects.in_bulk(list_ids).values()

all_opor = []
for o in obj_oportunidades:
    print o
    all_opor.append(o)

for i in list_ids:
    if i in all_opor:
        print all_opor.index(i)

Downside is that you have to get all the result rows first and store them before getting them in the order you want. (all_opor could be a dictionary above, with the table records stored in the values and the PKeys as dict keys.)

Other way, create a temp table with (Sort_Order, Pkey) and add that to the query:

Sort_Order        PKey
    1            31189
    2            31191
...

So when you sort on Sort_Order and Opor.objects, you'll get Pkeys it in the order you specify.

aneroid
  • 12,983
  • 3
  • 36
  • 66
  • Hello Aneroid, thanks for you answer. I don't think that I will be able to do that, let me say why... This code is in my view.py, and it should be returned to my template. In the template I should access the object values. So... The idea is to send the result of this query to template in the correctly order. – Thomas Sep 01 '12 at 20:20
  • Then the loop method above should work; instead of the query change. You would still have to predefine the order first in list_ids or get it some other way. And the values in the `all_opor` dict can hold the remaining values of the rest of the record. So `{PK_1: [col1, col2,...], PK_2: [col1, col2, ...]}` etc. and the order for PK_1, PK_2 is stored in `list_ids` which you loop through the 2nd time. Of course, [your answer](http://stackoverflow.com/a/12232526/1431750) the 2nd way (via SQL) but directly in django. – aneroid Sep 02 '12 at 04:28
1

I found a solution in: http://davedash.com/2010/02/11/retrieving-elements-in-a-specific-order-in-django-and-mysql/ it's suited me perfectly.

ids = [a_list, of, ordered, ids]
addons = Addon.objects.filter(id__in=ids).extra(
        select={'manual': 'FIELD(id,%s)' % ','.join(map(str,ids))},
        order_by=['manual'])

This code do something similiar to MySQL "ORDER BY FIELD".

Thomas
  • 2,256
  • 6
  • 32
  • 47
  • +1 Using the [`FIELD()`](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field) function is a great way to achieve this. Guess I got my "_Would have been nice to have this feature in SQL_" [above](http://stackoverflow.com/a/12230809/1431750). – aneroid Sep 03 '12 at 06:25
  • (btw, you should "accept" your own answer here since it's the ideal way to achieve this.) – aneroid Sep 03 '12 at 06:27
1

This guy: http://blog.mathieu-leplatre.info/django-create-a-queryset-from-a-list-preserving-order.html

Solved the problem for both MySQL and PostgreSQL!

If you are using PostgreSQL go to that page.

ruhanbidart
  • 4,564
  • 1
  • 26
  • 13