Return last N rows from table

Anvil Q&A
1

I’d like to instantiate a repeating panel with the last five rows from a data table.

What I’ve tried and what’s not working:
I’ve tried the following:
self.repeating_panel.items = app_tables.mytable.search(tables.order_by('date', ascending=True))[-5:]

But I get the following error:
ValueError: list slice indices and step cannot be negative

The other thing I tried was to sort the table the other way and take the first five rows:
self.repeating_panel.items = app_tables.mytable.search(tables.order_by('date', ascending=False))[:5]

This is a way to work around the list slice indices being negative. But now how would I reverse the order so that it is ascending=True again?

Or is there another approach you would recommend to populate a repeating panel with a certain number of the last entries in a table?

2

Getting the last 5 rows returned by a search requires Python to scan the whole table, read every row and discard them only to keep the last 5.

The correct way is to reverse the order and get the first 5 rows during the search. Then, since it’s just 5 rows, you can sort them once they are in memory.

In this little (untested) snippet I’m getting an iterator with 5 elements, converting it to list so it can be reversed and assigned to items:

last_5_rows = app_tables.mytable.search(tables.order_by('date', ascending=False))[:5]
last_5_rows = list(last_5_rows)
self.repeating_panel.items = reversed(last_5_rows)
2 Likes
3

Question about what goes on in the background…

If you index a search as you did here,

python will no longer scan the whole table? I don’t know what goes on in the background, but my intuition says you get a full copy of the table from the search and then create a segmented copy with the indexer. (My intuition is generally wrong)

4

The search iterator returned by app_tables.mytable.search does its job lazily, that is it will get what it needs to get as late as possible.

I don’t know the details, but it will very likely access the database and get a number of rows, keep them in cache and return them one by one. Then access the database again to get the net batch when it runs out of rows.

Accessing the database every time you need a row, would be too slow.
Accessing the database and getting all the rows you need in one shot, could run out of memory and it’s often useless, because you often need only the first few rows from a search.

You can crate an iterator that never stop returning stuff:

def all_positive_integers():
    n = 0
    while True:
        n += 1
        yield n

# this will never end
for x in all_positive_integers():
    print(x)

# this will run out of memory
l = list(all_positive_integers())

So, to address your specific case, the search will very likely get the first 100-ish rows that satisfy your search filter and sorting, then will slice them and get the first 5. If your search returns a million rows, this will work and be fast, because the 100 rows will go from the database to the interpreter, and the 5 rows only will reach the client.

Well, looking at your code, it looks like you are executing this code on the client, so I don’t know the details of the implementation, whether the 100 rows are all passed to the client or not.

I usually do the searches on server functions and return to the client only what the client needs, so I’m sure that the traffic is kept to a minimum and there are no vulnerabilities, because the form has no access to the database (has access to server functions that have access to the database and will filter the data returned to the form).

3 Likes
5

Thanks for taking the time to explain more.

That makes a lot more sense than my intuition. :slight_smile:

6

Thank you @stefano.menci Your explanation was very helpful.

1 Like