limitby question

[Richard]

Hello,

I may not understanding something... I am desapointed that I can't simply manipulate offset of the sql... Or should I just manipulate the limit...

I mean if I do repetitively thise query in sql :

select * form table order by desc limit 10 offset 0

select * form table order by desc limit 10 offset 10

select * form table order by desc limit 10 offset 20

select * form table order by desc limit 10 offset 30

I will get progressively all the records from the table 10 at a time...

But I can't simply do it with this query :

rows = db((db.table.id > 0)

              ).select(db.table.id, db.table.represent_field,

                       orderby=~db.table.id,

                       limitby=(10, offset if offset else 0))

Reading the book there seems to have planty of backend that don't support paging with limit and offset properly...

But still I find it frustrasting to have to struggle with the DAL and have to provide min and max for the offset to a limitby function which isn't a limit but an offset in reallity...

I solve it with this :

offset = request.vars.limit_by

if offset not in ('None', '', None) and offset.isdigit():

    offset = int(offset)

    min = offset - 10

    max = offset

else:

    offset = None

    min = 0

    max = 10

rows = db((db.table.id > 0)

              ).select(db.table.id, db.table.represent_field,

                       orderby=~db.table.id,

                       limitby=(min, max))

Richard

[Anthony]

What is your question? If you want to do an offset, you need two numbers -- either the two endpoints of the interval or one endpoint plus the size of the interval. Whichever pair of numbers you have, it is a single arithmetic operation to get the alternative pair. It might help if you explain what two numbers you would prefer to work with and where you are getting them.

Anthony

[Dave S]

On Monday, March 6, 2017 at 5:50:05 PM UTC-8, Anthony wrote:

What is your question? If you want to do an offset, you need two numbers -- either the two endpoints of the interval or one endpoint plus the size of the interval. Whichever pair of numbers you have, it is a single arithmetic operation to get the alternative pair. It might help if you explain what two numbers you would prefer to work with and where you are getting them.

Anthony

Is the issue perhaps cognitive dissonance caused by "min" and "max" being different from Richard's concept of "offset"?  I can see that if you're used to applying min and max to the values, you might be disturbed by applying them to the indices.

/dps

[Richard Vézina]

Yes sorry it was more of a complaint than a question

:)

I was disturbed by the fact that I can't leave the limit constant, like 10 rows, and only manipulate the offset as I would do in sql... Then I reread the book and understand min, max, but I am not sure I see the advantage... And I found confusing that limitby is more an offset parameter then a limit... I am not aware of all different backend implementation of paging and limitby may make more sens in the big picture... And I guess it make sens to have a particular method limitby which is not a perfect map of sql limit and offset...

If I got time, I will have look if we can some how have a limit and offset methods also, that would translate litteraly into their sql counter part to make it more intuitive and send a PR to the pyDAL and will see if it get included.

Richard

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Anthony]

On Tuesday, March 7, 2017 at 9:20:53 AM UTC-5, Richard wrote:

Yes sorry it was more of a complaint than a question

:)

I was disturbed by the fact that I can't leave the limit constant, like 10 rows, and only manipulate the offset as I would do in sql...

It sounds like you want something like:

LIMIT = 10
offset = request.vars.offset
rows = db(query).select(..., limitby=(LIMIT, offset))

But that isn't really much simpler than the actual DAL API, which is:

rows = db(query).select(..., limitby=(offset, offset + LIMIT))

What is the big difficulty with the above?

I'm not sure why the DAL uses (min, max) rather than (limit, offset), but I suspect the reason is that (min, max) is clearer when combining both values into a single parameter using a tuple. With a single tuple of values, it is natural for the first to be the min and the second the max, but the ordering of limit and offset would be somewhat arbitrary and open to confusion. If we wanted the API to accept a limit and an offset, it would probably be more clear to make those separate arguments.

Anyway, if it really bothers you, you can define a simple function:

def mylim(limit, offset):
    return (offset, offset + limit)

and use:

limitby=mylim(limit, offset)

Anthony

[Anthony]

For what it's worth, the Django ORM uses Python array slicing syntax (i.e., [start:stop]) to implement limit/offset, and SQLAlchemy uses .slice(start, stop) (though it also offers separate .limit() and .offset() methods). I don't think it is common to have a single argument of the form (limit, offset).

Anthony

[Richard Vézina]

Thanks for exploring this Anthony,

Yes, I guess having .limit() and .offset() would make it easier to use constant limit value like 10 records and just manipulate the offset...

I was just at first thinking that limitby=(limit, offset) or limitby=(offset, limit), I mean I intuitivelly want it to be like that, even if I had use limitby in the past, I use it for get last record for instance and never think about it much, I did just find how I can get the last result, and I was find with that, but now I am trying to feed an infinite scroll select html input and were needing to ajax get 10-20 records at a time to do so, and I at first was confused by limitby until I step back...

What confuse me the most at first was that my limitby parameters was alterated when I pass (10, 0) for instance to get the first 10 record (think limit, offset here) and get select with limit -10 and offset 10 or something like that...

Anyway thanks for being so helpful.

Richard

--