#33176: Unexpected results using order_by with multiple fields
-------------------------------------+-------------------------------------
Reporter: Mauro Crociara | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: order, order_by, | Triage Stage:
OrderBy, ordering | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Mauro Crociara:
Old description:
> 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?
New description:
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
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33176#comment:2>
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/065.3f1d1c09817dd2d3f52aa3c6be3dc3be%40djangoproject.com.
