How to get the last few records except the last record

[Maurice Waka]

If using the code: limitby=(0,10) am able to get the last 10 records,  Is there a way to get the same(last few records) except the very last record(last record posted to the db) from the db.
 I have tried:
records  = db(db.post.author == auth.user_id).select(orderby=(db.post.ALL, limitby=(0,-9))...getting a blank screen instead
regards

[sesenmaister]

records  = db(db.post.author == auth.user_id).select(orderby=(db.post.ALL, limitby=(0,10))[:-1]

[Maurice Waka]

Works well.

Great! 

--
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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/jOt3JLi8vJw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Anthony]

There's no need to select the extra record and then drop it via Python. Instead you can just change the range of the limitby tuple to get exactly the records you want.

Anthony

[Dave S]

On Thursday, October 26, 2017 at 7:26:48 AM UTC-7, Anthony wrote:

There's no need to select the extra record and then drop it via Python. Instead you can just change the range of the limitby tuple to get exactly the records you want.

 
Perhaps the issue is not knowing how many records there are.  Solvable by doing a count(), but then you're making two queries (to get the count, and then to get the records).

/dps

[Maurice Waka]

I don't understand this. Though I rectified as above and it works

[Anthony]

If limitby(0, 10) followed by slicing off the last record via [:-1] gets you the records you want, then so does limitby(0, 9) with no slicing, regardless of the overall record count.

Anthony

 

/dps

[Dave S]

What you say is true, but I took limitby(0,10) to be a specific example,  and that the general case was the real question, where you don't know how many records are involved.  On the other hand, I am familiar with the idea of just getting a snapshot of the most recent records; I do that in one of my projects, and a fixed limitby() works beautifully, given the proper orderby().

/dps
 

[Maurice Waka]

I had about 10 records. The more records I put in the more it did not work, i.e pick the last 10. I get the whole db records. I think the select().count() comes in handy here. Only how to work it out... 

--

[Anthony]

I'm not quite sure what you were going for with the original query, as it is not even valid Python syntax let alone valid DAL code, but if you want the 2nd through 9th most recent records, you could do:

db(db.post.author == auth.user_id).select(orderby=~db.post.modified_on, limitby=(1, 10))

The ~db.post.modified_on value for orderby sorts the records in descending ordering according to the modified time (assuming you have such a field in the table). The limitby value of (1, 10) then limits the results to the 2nd through 10th records in the sorted list.

If there is no field that stores the update time (or creation time) for each record, you could use orderby=~db.post.id, which generally should sort based on creation time (assuming the IDs in your database are ascending).

Anthony

[Anthony]

Perhaps the issue is not knowing how many records there are.  Solvable by doing a count(), but then you're making two queries (to get the count, and then to get the records).

If limitby(0, 10) followed by slicing off the last record via [:-1] gets you the records you want, then so does limitby(0, 9) with no slicing, regardless of the overall record count.

Anthony

What you say is true, but I took limitby(0,10) to be a specific example,  and that the general case was the real question, where you don't know how many records are involved.

I don't see a "general case" related to this particular issue that would require knowing the total record count (unless the general case is that the database does not support descending orderby).

Anthony

[Maurice Waka]

My mistake was that I used db.post.ALL...with your advice, db.post.id I now get the correct response. 

--

[Dave S]

Perhaps I was making the problem harder than I needed to, and overthinking the OP.

/dps