Changes between Initial Version and Version 2 of Ticket #28261


Ignore:
Timestamp:
Jun 1, 2017, 12:44:41 AM (7 years ago)
Author:
wqz
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #28261 – Description

    initial v2  
     1Background: I want to get the paging use slicing after ordering, if offset=20 and limit=10, code should be :
     2{{{
     3Computer.objects.select_related().order_by('-installed_agent')[20: 30].values_list('id', flat=True)
     4}}}
     5
     6But look:
     7{{{
     8In [27]: list(Computer.objects.select_related().order_by('-installed_agent')[20: 30].values_list('id', flat=True))
     9Out[27]: [1612, 1613, 1614, 1615, 1616, 1617, 1618, 1619, 1620, 3221]
     10 
     11In [28]: list(Computer.objects.select_related().order_by('-installed_agent')[10: 20].values_list('id', flat=True))
     12Out[28]: [1612, 1613, 1614, 1615, 1616, 1617, 1618, 1619, 1620, 3221]
     13}}}
     14
     15If array[20: 30] and array[10: 20] return the same result,the result of the paging is wrong, why this?
     16
     17Now django automatically generated sql for "Computer.objects.select_related().order_by('-installed_agent')[20: 30].values_list('id', flat=True)" is:
     18{{{
     19SELECT *
     20  FROM (SELECT "_SUB".*, ROWNUM AS "_RN"
     21          FROM (SELECT "OM_COMPUTER"."ID" AS Col1
     22                  FROM "OM_COMPUTER"
     23                 ORDER BY "OM_COMPUTER"."INSTALLED_AGENT" DESC) "_SUB"
     24         WHERE ROWNUM <= 30)
     25WHERE "_RN" > 20
     26}}}
     27
     28If the generate sql like this:
     29{{{
     30SELECT SUB2.*
     31  FROM (SELECT SUB1.*, ROWNUM AS "_RN"
     32          FROM (SELECT "OM_COMPUTER"."ID" AS COL1
     33                  FROM "OM_COMPUTER"
     34                 ORDER BY "OM_COMPUTER"."INSTALLED_AGENT" DESC) SUB1) SUB2
     35WHERE "_RN" <= 30
     36   AND "_RN" > 20
     37}}}
     38
     39Then the result of the paging is right.
     40
     41test:
     42
     43{{{
     44
     45In [3]: list(Computer.objects.select_related().order_by('-installed_agent')[:10].values_list('id', flat=True))
     46Out[3]: [8148, 7042, 7666, 6027, 6029, 8160, 8161, 8164, 8165, 8171]
     47 
     48In [5]: list(Computer.objects.select_related().order_by('-installed_agent')[10 :20].values_list('id', flat=True))
     49Out[5]: [8173, 8174, 8175, 8176, 8177, 8180, 8181, 8186, 8187, 4397]
     50 
     51In [6]: list(Computer.objects.select_related().order_by('-installed_agent')[20 :30].values_list('id', flat=True))
     52Out[6]: [4408, 4419, 4430, 4441, 4458, 8087, 8090, 8091, 8093, 8096]
     53
     54In [10]: list(Computer.objects.select_related().order_by('-installed_agent')[10 :].values_list('id', flat=True))
     55Out[10]:
     56[8173,
     57 8174,
     58 8175,
     59...
     60]
     61
     62In [7]: str(Computer.objects.select_related().order_by('-installed_agent')[:10].values_list('id', flat=True).query)
     63Out[7]: 'SELECT "__SUB".* FROM (SELECT "_SUB".*,ROWNUM AS "_RN" FROM (SELECT "OM_COMPUTER"."ID" AS Col1 FROM "OM_COMPUTER" ORDER BY "OM_COMPUTER"."INSTALLED_AGENT" DESC) "_SUB") "__SUB" WHERE "_RN" <= 10 AND "_RN" > 0'
     64 
     65In [8]: str(Computer.objects.select_related().order_by('-installed_agent')[10 :].values_list('id', flat=True).query)
     66Out[8]: 'SELECT "__SUB".* FROM (SELECT "_SUB".*,ROWNUM AS "_RN" FROM (SELECT "OM_COMPUTER"."ID" AS Col1 FROM "OM_COMPUTER" ORDER BY "OM_COMPUTER"."INSTALLED_AGENT" DESC) "_SUB") "__SUB" WHERE "_RN" > 10'
     67 
     68In [9]: str(Computer.objects.select_related().order_by('-installed_agent')[20 :30].values_list('id', flat=True).query)
     69Out[9]: 'SELECT "__SUB".* FROM (SELECT "_SUB".*,ROWNUM AS "_RN" FROM (SELECT "OM_COMPUTER"."ID" AS Col1 FROM "OM_COMPUTER" ORDER BY "OM_COMPUTER"."INSTALLED_AGENT" DESC) "_SUB") "__SUB" WHERE "_RN" <= 30 AND "_RN" > 20'
     70}}}
     71
     72works good.
    173detail in PR with address https://github.com/django/django/pull/8580
Back to Top