#32297: QuerySet.get() method not working as expected with Window functions
-------------------------------------+-------------------------------------
Reporter: Jerin Peter George | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by starryrbs):
If you use qs.get(pk=pk).rank, the generated SQL statement is like this:
{{{
SELECT "expressions_window_employee"."id",
"expressions_window_employee"."name",
"expressions_window_employee"."salary",
"expressions_window_employee"."department",
"expressions_window_employee"."hire_date",
"expressions_window_employee"."age",
"expressions_window_employee"."classification_id",
"expressions_window_employee"."bonus", RANK() OVER (ORDER BY
"expressions_window_employee"."salary" DESC) AS "rank" FROM
"expressions_window_employee" WHERE "expressions_window_employee"."id" =
11
}}}
If you want to query the value of rank, you can do this:
{{{
rank_dict = {item['pk']:item['rank'] for item in qs.values("pk", "rank")}
rank_dict.get(12)
}}}
Replying to [comment:2 Jerin Peter George]:
> Test case to reproduce the issue
>
>
> {{{
> import datetime
> from decimal import Decimal
>
> from django.db.models import F, Window
> from django.db.models.functions import Rank
> from django.test import TestCase
>
> from .models import Employee
>
>
> class WindowFunctionTests(TestCase):
> @classmethod
> def setUpTestData(cls):
> Employee.objects.bulk_create([
> Employee(
> name=e[0],
> salary=e[1],
> department=e[2],
> hire_date=e[3],
> age=e[4],
> bonus=Decimal(e[1]) / 400,
> )
> for e in [
> ('Jones', 45000, 'Accounting', datetime.datetime(2005,
11, 1), 20),
> ('Williams', 37000, 'Accounting',
datetime.datetime(2009, 6, 1), 20),
> ('Jenson', 45000, 'Accounting', datetime.datetime(2008,
4, 1), 20),
> ('Adams', 50000, 'Accounting', datetime.datetime(2013,
7, 1), 50),
> ('Smith', 55000, 'Sales', datetime.datetime(2007, 6, 1),
30),
> ('Brown', 53000, 'Sales', datetime.datetime(2009, 9, 1),
30),
> ('Johnson', 40000, 'Marketing', datetime.datetime(2012,
3, 1), 30),
> ('Smith', 38000, 'Marketing', datetime.datetime(2009,
10, 1), 20),
> ('Wilkinson', 60000, 'IT', datetime.datetime(2011, 3,
1), 40),
> ('Moore', 34000, 'IT', datetime.datetime(2013, 8, 1),
40),
> ('Miller', 100000, 'Management', datetime.datetime(2005,
6, 1), 40),
> ('Johnson', 80000, 'Management', datetime.datetime(2005,
7, 1), 50),
> ]
> ])
>
> def test_rank_with_queryset_get_method(self):
> qs = Employee.objects.annotate(
> rank=Window(expression=Rank(), order_by=F("salary").desc()),
> )
> rank_set = list(qs.values_list("pk", "rank"))
> rank_set_iter = [(emp.pk, emp.rank) for emp in qs]
>
> self.assertEqual(rank_set, rank_set_iter) # does queryset
iteration has any problem?
> for pk, rank in rank_set:
> self.assertEqual(qs.get(pk=pk).rank, rank) # does `.get()`
has any problem?
> }}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32297#comment:3>
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/074.97f64c29ad7d4168c4ff8bb077ebf200%40djangoproject.com.
