[Django]-How not to order a list of pk's in a query?

by

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

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

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

Leave a comment