On 31 July 2013 07:56, Florian Apolloner 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
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+unsubscribe@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.

Search Discussions

Discussion Posts


Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 10 of 19 | next ›
Discussion Overview
groupdjango-developers @
categoriesdjango, python
postedJul 30, '13 at 8:37p
activeAug 1, '13 at 12:04p



site design / logo © 2021 Grokbase