On 31 July 2013 07:56, Florian Apolloner <f.apollo...@gmail.com> wrote:
> Hi Wim,
>
>
> On Wednesday, July 31, 2013 12:04:42 AM UTC+2, Wim Lewis wrote:
>>
>> On 30 Jul 2013, at 2:06 PM, Florian Apolloner wrote:
>> > How do you think such support would look like? For negative indices
>> > you'd have to know the size of the resultset to be able to do "limit
>> > somthing offset length-your_negative_index" -- this doesn't seem to make
>> > any
>> > sense for an ORM. You can always do list(qs)]:-1] though…
>>
>> It seems like the first comment in the ticket answers that question.
>> Django would reverse the sense of the query's ordering clause and use a
>> simple LIMIT.
>
>
> In my opinion it doesn't; eg imagine the following as query:
> MyModel.objects.order_by('whatever')[0:-50]; this isn't translateable into
> MyModel.objects.order_by('-whatever')[50:] (the issue here is that the end
> is now again undefined) since at least SQLite doesn't support an OFFSET
> clause without a LIMIT. Also I think it's more natural to rewrite the
> ordering of the query yourself to express that instead of using negative
> ranges.
At first glance, there's no important difference in offsets from one
end of an array or the other.
ORDER BY x ASC LIMIT 10 gives us the first 10 results in the ordered
list of result rows.
ORDER BY x DESC LIMIT 10 gives us the last 10 results in the ordered
list of result rows.
except that the overall ordering of output rows is different. So there
*is* a qualitative difference in negative indexing and I can see why
people think they want it. But there are a few problems since SQL
Standard does not provide a mechanism for this nor are any DBMS I'm
aware of tuned for that type of request. I understand why that is,
since "scroll all the way to end of result set" type logic isn't too
smart with arbitrary result set sizes that we see on databases,
whereas with Python array sizes are bounded much more significantly.
To implement this just in Django could be done in the following way.
If you want to see the data in one ordering (ASC), yet define the
LIMIT in another ordering you can either
1) retrieve via ASC: scroll all the way to the end of the result set,
then step back $LIMIT and read from there.
2) retrieve via DESC, apply LIMIT and then re-sort by ASC using two queries
3) retrieve via DESC, apply LIMIT and then re-sort by ASC using derived table
4) implement core logic for that into PostgreSQL
(1) would be very expensive with large result sets and should be avoided
(2) would optimise better but is hard to define generically.
(3) would require two sorts on the data like this
SELECT * FROM (rest-of-query ORDER BY ... DESC LIMIT n ) ORDER BY ... ASC
(4) though I'm not sure there'd be much interest in a not-very-useful
and non-standard SQL feature that would be non-trivial to implement
Django could support negative indexes, but it would require some
complex SQL that might not optimise well so it seems worth avoiding.
The order_by('-whatever') doesn't optimise well since this is a
function on the whatever column, which will defeat any attempt to
utilise an index to supply the ordering. i.e. it results in a
sequential scan and should be avoided.
I'd suggest an explicit option to change the ordering ASC | DESC,
which is matched by a SQL Standard feature.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
For more options, visit https://groups.google.com/groups/opt_out.
