views:

103

answers:

2

I'm having a attribute on my model to allow the user to order the objects. I have to update the element's order depending on a list, that contains the object's ids in the new order; right now I'm iterating over the whole queryset and set one objects after the other. What would be the easiest/fastest way to do the same with the whole queryset?

def update_ordering(model, order):
    """ order is in the form [id,id,id,id] for example: [8,4,5,1,3] """
    id_to_order = dict((order[i], i) for i in range(len(order)))
    for x in model.objects.all():
        x.order = id_to_order[x.id]
        x.save()
+1  A: 

I don't know if it is possible to do this using one query, but this is more efficient in any case:

def update_ordering(model, order):
    """ order is in the form [id,id,id,id] for example: [8,4,5,1,3] """
    for id in model.objects.values_list('id', flat=True):
        model.objects.filter(id=id).update(order=order.index(id))
Collin Anderson
Guess it only saves hitting the db once to get all the items (all items have to be updated, so your solution also iterates over all of them).
lazerscience
+1  A: 

This cannot be done in one single queryset operation. As far as I know it can't even be done in one query with raw SQL. So you will always need the update call for each object that has to be updated. So both your and Collin Anderson's solutions seem quite optimal for your description.

However, what's your use case? Is really the whole list going to change every time? In most situations this seems very unlikely. I can see some different approaches.

You save the order field like you say, but you generate a diff for the order list:def

update_ordering(model, order):
    """ order is in the form [id,id,id,id] for example: [8,4,5,1,3] """
    original_order = model.objects.value_list('id', flat=True).order_by('order')
    order = filter( lambda x: x[1]!=x[2], zip(xrange(len(order)), order, original_order))
    for i in order:
        model.objects.filter(id=i[1]).update(order=i[0])

Another approach, depending on what you're making is to do a partial update (e.g. using AJAX) if possible, instead of updating the whole re-ordered set, just update every update separately. This will often increase the total load, but will spread it more over time. Take for example moving the 5th element step-by-step to place 2, this will introduce 3 swaps: (5,4); (4,3); (3,2). Resulting in 6 updates, while with the all-in-one-time approach only 4 will be needed. But the small operations will be spread over time.

KillianDS