5

I am trying to create a Django queryset that returns records in a specific order that matches a list of IDs.

My list of IDs is:

[34,12,4,89,3,67,11]

If I run the following query:

queryset = Item.objects.filter(pk__in=[34,12,4,89,3,67,11])

The query returns records in the ID order:

3,4,11,12,34,67,89

Is there any way to make the query return the records in the same order as the list?

If not is there a way to re-order the query result into the required order?

Bill Noble
  • 6,466
  • 19
  • 74
  • 133
  • Is the ordering you've determined (34, 12, 4, ...) related to an attribute on the model at all? – Taylor D. Edmiston May 08 '16 at 20:37
  • Here's a potentially related question -- http://stackoverflow.com/questions/883575/custom-ordering-in-django, though I don't think what you're looking for is possible without casting the queryset into an object like a list, then manually sorting that, for example with a custom lambda. – Taylor D. Edmiston May 08 '16 at 20:45
  • No the id list is not related to the model. – Bill Noble May 08 '16 at 20:51
  • Without knowing more about your use case, another consideration might be to add this custom order as a (unique) int field on the model. One advantage of this is separating the data from your code (so that it could be changed in the Django ORM, for instance). – Taylor D. Edmiston May 08 '16 at 20:57
  • How would one add a custom order as a unique field? How would that work? – Bill Noble May 08 '16 at 21:21
  • I'm going to add this an answer instead because comments don't support code blocks. – Taylor D. Edmiston May 08 '16 at 21:46

1 Answers1

2

Note: Adding this as an answer because OP asked about the approach in the comments of the original question. Showing the snippets in comments didn't allow fenced code blocks.

In the comments on the original question, I mentioned an alternative solution storing your order numbers in a model field, so that ordered queries would be simpler.

One use case for this is if you have a queue of objects where each item is a movie and the user is deciding which order to watch them. You might want to let a Django admin or a user re-order the items (hence making it a field). For the sake of a simple example let's assume there is only one user so we'll build the ordering into the model, as opposed to making a custom table to join through Item and User models.

The model:

class Item(models.Model):
    # ...
    my_order = models.PositiveIntegerField(unique=True)

Set the attribute either at creation or later:

Item.objects.create(id=34, my_order=1)
Item.objects.create(id=12, my_order=2)
Item.objects.create(id=4, my_order=3)
# ...

Of course, you could create them and set my_order in any order you wished, and the result will still be the same.

Retrieve the objects in desired order:

queryset = Item.objects.order_by('my_order')
Taylor D. Edmiston
  • 12,088
  • 6
  • 56
  • 76
  • That solution would be ok if I wanted to return items in a pre specified order but my lists of IDs will change every time a search is done. – Bill Noble May 09 '16 at 08:13