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?

Using base_manager works to:


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

And 

{{{#!python
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)
}}}
{{{#!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"".""task_type_two"" ASC, ""candidates_tasks"".""planning_rsc"" DESC
}}}

I cannot use **only** with **object**


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


{{{#!python
Task.objects.filter(candidate=candidate, planning_rsc__isnull=False).values_list('pk').order_by('-planning_rsc', 'task_type_two')
}}}
{{{#!sql
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
"	Bug	closed	Database layer (models, ORM)	3.2	Normal	needsinfo	order, order_by, OrderBy, ordering		Unreviewed	0	0	0	0	0	0