What is the best way to use query with a list and keep the list order?

Question

I've searched online and could only find one blog that seemed like a hackish attempt to keep the order of a query list. I was hoping to query using the ORM with a list of strings, but doing it that way does not keep the order of the list.

From what I understand bulk_query only works if you have the id's of the items you want to query.

Can anybody recommend an ideal way of querying by a list of strings and making sure the objects are kept in their proper order?

So in a perfect world I would be able to query a set of objects by doing something like this...

Entry.objects.filter(id__in=['list', 'of', 'strings'])

However, they do not keep order, so string could be before list etc...

The only work around I see, and I may just be tired or this may be perfectly acceptable I'm not sure is doing this...

for i in listOfStrings:
    object = Object.objects.get(title=str(i))
    myIterableCorrectOrderedList.append(object)

Thank you,

Could you give an example of the query and what you are expecting to get? I can picture something in my mind but I don't know if what I'm picturing is what you are actually asking. — Louis, Apr 09 '14 at 23:08
I've updated my question, basically my solution works it just seems like there must be something better out there? — Chris Hawkes, Apr 09 '14 at 23:32
I saw that answer but it did not seem to apply. Basically I have a list of movies, it's a favorite list for somebody, to query that list I need those to be in the order they gave them. (by favorite) they can't be sorted alphabetically, by id, etc... I could have built a manytomany table for their favorite movie rankings, but it was somewhat of an afterthought. — Chris Hawkes, Apr 09 '14 at 23:35

score 3 · Accepted Answer · edited May 23 '17 at 12:05

The problem with your solution is that it does a separate database query for each item.

This answer gives the right solution if you're using ids: use in_bulk to create a map between ids and items, and then reorder them as you wish.

If you're not using ids, you can just create the mapping yourself:

values = ['list', 'of', 'strings']

# one database query
entries = Entry.objects.filter(field__in=values)

# one trip through the list to create the mapping
entry_map = {entry.field: entry for entry in entries}

# one more trip through the list to build the ordered entries
ordered_entries = [entry_map[value] for value in values]

(You could save yourself a line by using index, as in this example, but since index is O(n) the performance will not be good for long lists.)

Wow that is a perfect example and it worked great. I would not have gotten that by myself. Thank you, long live Python and Django! (coming from a .NET developer) I saw Roseman's example but did not think it applied to this specific issue and it certainly was not laid out as perfectly as your example. Thanks again, i think this will be hugely helpful for others out there. — Chris Hawkes, Apr 10 '14 at 00:10

score 2 · Answer 2 · edited May 23 '17 at 12:05

Remember that ultimately this is all done to a database; these operations get translated down to SQL somewhere.

Your Django query loosely translated into SQL would be something like:

SELECT * FROM entry_table e WHERE e.title IN ("list", "of", "strings");

So, in a way, your question is equivalent to asking how to ORDER BY the order something was specified in a WHERE clause. (Needless to say, I hope, this is a confusing request to write in SQL -- NOT the way it was designed to be used.)

You can do this in a couple of ways, as documented in some other answers on StackOverflow [1] [2]. However, as you can see, both rely on adding (temporary) information to the database in order to sort the selection.

Really, this should suggest the correct answer: the information you are sorting on should be in your database. Or, back in high-level Django-land, it should be in your models. Consider revising your models to save a timestamp or an ordering when the user adds favorites, if that's what you want to preserve.

Otherwise, you're stuck with one of the solutions that either grabs the unordered data from the db then "fixes" it in Python, or constructing your own SQL query and implementing your own ugly hack from one of the solutions I linked (don't do this).

tl;dr The "right" answer is to keep the sort order in the database; the "quick fix" is to massage the unsorted data from the database to your liking in Python.

EDIT: Apparently MySQL has some weird feature that will let you do this, if that happens to be your backend.

What is the best way to use query with a list and keep the list order?

2 Answers2