id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
33176	Unexpected results using order_by with multiple fields	Mauro Crociara	nobody	"I'm facing unexpected results when I switch fields `-planning_rsc` and `task_type_two` in **order_by** clause.

{{{#!python
Task.objects.filter(candidate=task.candidate, planning_rsc__isnull=False).only('pk').order_by('-planning_rsc', '-task_type_two')
}}}

produces


{{{#!sql
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:

{{{#!python
Task.objects.filter(candidate=task.candidate, planning_rsc__isnull=False).only('pk').order_by('task_type_two', '-planning_rsc')
}}}

I get:

{{{#!sql
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:

{{{#!python
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


{{{#!sql
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:

{{{#!python
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?"	Bug	new	Database layer (models, ORM)	3.2	Normal		order, order_by, OrderBy, ordering		Unreviewed	0	0	0	0	0	0