What I’m trying to do:
Hi team,
I am building a car advertising website. Assuming I have a database table with 25,000 rows, i wouldn’t want to load all the rows when the user opens the homepage.
How can i break it up such that i have different pages each loading only 50 rows and the user can click next to access the next 50, so that the query limits the response to 50 rows?
Many thanks for your time.
Hello,
You would likely use the built in pagination or rig up your own pagination. Anvil gives you total control over how much data you return to the client and when.
If you have not already, I would suggest going through the tutorials on data grids and data tables, and their related docs.
Perhpas whip up a small prototype which will help guide to specifics.
1 Like
Thank you for your reply. Assuming i have the following code and server function
self.repeating_panel_1.items = anvil.server.call('fetchAllCars')
@anvil.server.callable
def fetchAllCars():
searchResult = app_tables.vehicles.search(tables.order_by('created', ascending=False), isCurrent=True)
return searchResult
and i have 10,000 cars in the database, won’t it take forever to load 10,000 rows onto the repeating panel?
Yes if you use a repeating panel, no if you use a DataGrid and enable pagination.
The server callable doesn’t return a list of rows, it returns a smart iterator object that will lazily load what’s needed in chunks of (I think) 100 rows at a time. The DataGrid will show the first page, then, if you go the the next page and enough rows are already loaded, they are immediately shown, otherwise it will automatically load the next chunk of 100 and show the next page.
If you try to access the last page, you could have the problem you mentioned, because the iterator will load all rows and finally show the last page.
You could have more control by hiding the automatic buttons and creating your own.
And you could have even more control by returning lists rather than iterator objects. In this case you could tell the server function what page to return and only return the rows you need.
There are even more advanced approaches. See here for one example: Auto Scroll - Automatically add content as the user scrolls the mouse wheel
2 Likes
Could you kindly give me an example of how to do this?
Along the lines of:
def fetch_entries(entries, iterator):
"""
Fetch a specified number of entries from an iterator.
:param entries: The number of entries to fetch.
:param iterator: The iterator object to fetch the entries from.
:return: A list of up to 'entries' elements from the iterator.
"""
result_list = []
try:
for _ in range(entries):
result_list.append(next(iterator))
except StopIteration:
# This block catches the StopIteration exception, which is raised if the iterator
# is exhausted (i.e., has fewer elements than 'entries').
# This means the loop has tried to fetch more items than available, so we just end the loop.
pass
return result_list
# Usage example
rows = app_tables.data.search() # This is your search iterator object.
first_50_entries = fetch_entries(50, rows)
1 Like
Here is an example of how a search is sliced, so a number of rows is skipped and the exact number required by the current page is returned, then the list comprehension transforms the row object iterator into a list of dictionaries.
# on the client: get the 5th page
self.repeating_panel_1.items = anvil.server.call('fetchAllCars', 4)
# on the server
PAGE_SIZE = 50
@anvil.server.callable
def fetchAllCars(page_number):
search_result = app_tables.vehicles.search(tables.order_by('created', ascending=False), isCurrent=True)
return [dict(row) for row in search_result[page_number:page_number + PAGE_SIZE]]
You could also add some fine tuning regarding what to fetch. For example you may want to exclude some columns with fetch_only (see here).
You could also build a custom dictionary, where you add columns that don’t exist in the database, something like this. Here the table could have 10 columns, but we are not wasting time fetching all of them, we only fetch the 4 we need. Then we build dictionaries with 3 items each, because that’s what the DataGrid will show (yeah, I decided that knowing the number of km per year would be useful, whatever that means, sorry, not my best creative moment):
search_result = app_tables.vehicles.search(tables.order_by('created', ascending=False),
q.fetch_only('model', 'cost', 'km', 'year'), isCurrent=True)
return [{
'name': row['model'],
'cost': row['cost'],
'km/year': f"{row['km'] / row['year']:0.2f}",
} for row in search_result[page_number:page_number + PAGE_SIZE]]
1 Like
@stefano.menci Thank you for your reply.
Below was my earlier attempt…
self.start = 0
self.end = 50
self.mylist = anvil.server.call('fetchAllCars')
self.repeating_panel_1.items = self.mylist[self.start:self.end]
def link_next_click(self, **event_args):
"""This method is called when the link is clicked"""
self.start += 50
self.end += 50
self.repeating_panel_1.items = self.mylist[self.start:self.end]
so initially i load the search iterator, and slice 50 rows from it and present this to the repeating panel. then, i have a link for the user to click when they want to see the next 50 cars which repeats the same process as above.
Does it matter if you slice on the client side?
@socint
Thank you for your reply. Below was my attempt earlier:
self.start = 0
self.end = 50
self.mylist = anvil.server.call('fetchAllCars')
self.repeating_panel_1.items = self.mylist[self.start:self.end]
def link_next_click(self, **event_args):
"""This method is called when the link is clicked"""
self.start += 50
self.end += 50
self.repeating_panel_1.items = self.mylist[self.start:self.end]
so initially i load the search iterator, and slice 50 rows from it and present this to the repeating panel. then, i have a link for the user to click when they want to see the next 50 cars which repeats the same process as above.
Does it matter if you slice on the client side?
Should be fine on the client once you’ve loaded it through.
1 Like