Unexpected results using order_by with multiple fields

[Mauro Crociara]

I'm facing unexpected results when I switch fields -planning_rsc and task_type_two in order_by clause.

Task.objects.filter(candidate=task.candidate, planning_rsc__isnull=False).only('pk').order_by('-planning_rsc', 'task_type_two')

produces

SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
WHERE ("candidates_tasks"."candidate_id" = 77677 AND "candidates_tasks"."planning_rsc" IS NOT NULL)
GROUP BY "candidates_tasks"."id"
ORDER BY "candidates_tasks"."planning_rsc" DESC

But doing this:

Task.objects.filter(candidate=task.candidate, planning_rsc__isnull=False).only('pk').order_by('task_type_two', '-planning_rsc')

I get:

SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
WHERE ("candidates_tasks"."candidate_id" = 77677 AND "candidates_tasks"."planning_rsc" IS NOT NULL)
GROUP BY "candidates_tasks"."id",
         CASE
             WHEN "candidates_tasks"."task_type_two" = 01_site_implementation THEN 01 - Site Implementation
             WHEN "candidates_tasks"."task_type_two" = 02_technology_upgrade THEN 02 - Technology Upgrade
             WHEN "candidates_tasks"."task_type_two" = 03_configuration_change THEN 03 - Configuration Change
             WHEN "candidates_tasks"."task_type_two" = 98_relocation THEN 98 - Relocation
             WHEN "candidates_tasks"."task_type_two" = 99_dismission THEN 99 - Dismission
             ELSE NULL END
ORDER BY CASE
             WHEN "candidates_tasks"."task_type_two" = 01_site_implementation THEN 01 - Site Implementation
             WHEN "candidates_tasks"."task_type_two" = 02_technology_upgrade THEN 02 - Technology Upgrade
             WHEN "candidates_tasks"."task_type_two" = 03_configuration_change THEN 03 - Configuration Change
             WHEN "candidates_tasks"."task_type_two" = 98_relocation THEN 98 - Relocation
             WHEN "candidates_tasks"."task_type_two" = 99_dismission THEN 99 - Dismission
             ELSE NULL END ASC, "candidates_tasks"."planning_rsc" DESC

As you can see, both task_type_two and planning_rsc have been included in order_by clause. To solve the problem, I had to use OrderBy expression this way:

Task.objects.filter(candidate=task.candidate, planning_rsc__isnull=False).only('pk').order_by(OrderBy(F('planning_rsc'), descending=True), OrderBy(F('task_type_two')))

Which produces the desired result

SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
WHERE ("candidates_tasks"."candidate_id" = 77677 AND "candidates_tasks"."planning_rsc" IS NOT NULL)
ORDER BY "candidates_tasks"."planning_rsc" DESC, "candidates_tasks"."task_type_two" ASC

The fields planning_rsc and task_type_two are defined in model like here:

task_type_two = models.CharField(
        verbose_name=_("Task type"), choices=choices.TASK_TYPE_CHOICES, max_length=64, blank=True,
        help_text=_("The type of the task"))
planning_rsc = models.DateField(
        _('Planning RSC Date'), null=True, blank=True, help_text='The Planning RSC Date of the task')

I'm doing something wrong?

Using base_manager works to:

Task._meta.base_manager.select_related(None).prefetch_related(None).filter(candidate=candidate, planning_rsc__isnull=False).only('pk').order_by('-planning_rsc', 'task_type_two')

SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
WHERE ("candidates_tasks"."candidate_id" = 77677 AND "candidates_tasks"."planning_rsc" IS NOT NULL)
ORDER BY "candidates_tasks"."planning_rsc" DESC, "candidates_tasks"."task_type_two" ASC

And

Task._meta.base_manager.select_related(None).prefetch_related(None).filter(candidate=candidate, planning_rsc__isnull=False).only('pk').order_by('task_type_two', '-planning_rsc').query)

SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
WHERE ("candidates_tasks"."candidate_id" = 77677 AND "candidates_tasks"."planning_rsc" IS NOT NULL)
ORDER BY "candidates_tasks"."task_type_two" ASC, "candidates_tasks"."planning_rsc" DESC

I cannot use only with object

Task.objects.filter(candidate=candidate, planning_rsc__isnull=False).only('pk').order_by('-planning_rsc', 'task_type_two')

django.core.exceptions.FieldError: Field Task.candidate cannot be both deferred and traversed using select_related at the same time.

Then try with values_list

Task.objects.filter(candidate=candidate, planning_rsc__isnull=False).values_list('pk').order_by('-planning_rsc', 'task_type_two')

SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
         LEFT OUTER JOIN "candidates_technologiestwo"
                         ON ("candidates_tasks"."id" = "candidates_technologiestwo"."task_id")
         INNER JOIN "candidates_candidates"
                    ON ("candidates_tasks"."candidate_id" = "candidates_candidates"."location_ptr_id")
         LEFT OUTER JOIN "iliad_operators" ON ("candidates_candidates"."landlord_operator_id" = "iliad_operators"."id")
         LEFT OUTER JOIN "candidates_tower_companies"
                         ON ("candidates_candidates"."tower_company_id" = "candidates_tower_companies"."id")
         INNER JOIN "iliad_locations" ON ("candidates_candidates"."location_ptr_id" = "iliad_locations"."id")
WHERE ("candidates_tasks"."candidate_id" = 77677 AND "candidates_tasks"."planning_rsc" IS NOT NULL)
GROUP BY "candidates_tasks"."id"
ORDER BY "candidates_tasks"."planning_rsc" DESC

task_type_two is missing in order_by clause