efficient sort on combined django querysets

Question

I am trying to achieve very complex kind of sort using django queryset.Its more like how your comments and your friends and family comments appears on top in facebook and youtube comment.

to breakdown, i am combining different queryset to same model achieve a result.

my_assinged_tasks = Tasks.objects.filter(to=request.user)
non_assigned_tasks = Tasks.objects.filter(to__isnull=True)
tasks_created_by_my_direct_supervisor = Tasks.objects.filter(**criteria)
suggested_todo_task = ##Some Complex Logic to detect what task are similar to my completed tasks
uncompleted_tasks = ## Criteria

tasks = my_assinged_tasks | non_assigned_tasks | tasks_created_by_my_direct_supervisor | suggested_todo_task | uncompleted_tasks

I want to sort it in order of
1 - tasks_created_by_my_direct_supervisor
2 - my_assinged_tasks
3 - uncompleted_tasks
......

the only solution i have in my mind is to create a dict and loop through each query set result individually and populate that dict accordingly which is very in efficient that way.

Is there a more efficient way of achieving this?

Possible duplicate of [Display objects from different models at the same page according to their published date](http://stackoverflow.com/questions/37747427/display-objects-from-different-models-at-the-same-page-according-to-their-publis) — e4c5, May 08 '17 at 13:30

score 0 · Answer 1 · answered May 08 '17 at 12:34

It looks like you already have the tasks in the sets that you want. Depending on how you process the tasks after getting them maybe you could just use itertools.chain to return them in that order.

tasks = itertools.chain(
    tasks_created_by_my_direct_supervisor, 
    my_assinged_tasks, 
    uncompleted_tasks, 
    ...
)

itertools.chain takes multiple lists and yields the elements as one single iterable.

Eugene Morozov · Answer 2 · 2017-05-08T20:47:29.733

You can use the following:

from django.db.expressions import RawSQL

Task.objects.annotate(empty_to=RawSQL('to IS NULL', [])).order_by('empty_to')

You can add multiple annotations and sort by them as you wish. RawSQL annotation can receive arguments if you need to filter by a particular user id, for example. You can use SQL statements like IF, CASE, COALESCE to generate desired value for the annotated column you'll use for ordering.

This way you'll achieve two goals:

Results would be filtered and sorted by the database engine (generally more efficient)
Results would be a single Django QuerySet, no need to deserialize them into dictionaries, they will be already in the proper order

efficient sort on combined django querysets

2 Answers2