[Answer]-How to order Django Queryset by a specified match and then default to the original ordering for all unmatched items?

by

1👍

This is a pretty complex sort. I’m going to take a stab at a solution. You definitely need to do some raw sql, so I am going to use PostGres for an example. First, you need to add a column in the query to do the sorting on. Then, just sort on that column. This is my example:

Book.object.extra(
    select={'sort': 'CASE WHEN name LIKE %s THEN 0 ELSE array_length(string_to_array(name, ','), 1) + 1 END',
    select_params=['%' + requestedLanguage + '%']
).order_by('sort')

A few things to note:

  1. If the requestedLanguage is in the languages_available comma separated languages, that Book will be ordered first. (order 0)
  2. Next, everything else will be sorted by the count of the languages_available.
  3. As for the third sort you requested, I’m not sure what you want exactly. I’m confused because the field is actually a list of languages.
  4. I think it might be easier to sort if the languages_available was a manytomany field to another table of languages.
  5. Finally, I’m not sure if you can use .order_by() on a column that was created in .extra() you might need to use a .raw() query.

I’m not sure if this will work for you at all, but hopefully it will push you in the right direction.

👤jproffitt

Leave a comment