#33176: Unexpected results using order_by with multiple fields
-------------------------------------+-------------------------------------
Reporter: Mauro | Owner: nobody
Crociara |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: order, order_by,
Severity: Normal | OrderBy, ordering
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/33176>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.7852b9a5af80f10309a87c5e71ecc9a6%40djangoproject.com.
