All the examples with the DataGrid use list comprehensions, which are very flexible with small datasets, but cannot be used with tens of thousands of rows.
I tried to create a slicer class on the client side so I could use the DataGrid with large datasets, but I got this error:
SuspensionError: Cannot call a function that blocks or suspends here
at Form1, line 9
The idea was to assign self.repeating_panel_1.items = DataGridSlicer(). The DataGridSlicer.__getitem__ would call a server function that uses SQL’s LIMIT and OFFSET to return only the required slice rather than the whole pie.
I don’t really know if the approach I am taking is the correct one, because I got stuck here and I couldn’t experiment more.
Do you see anything wrong with my approach?
Why does it fail?
EDIT
I just realized that a DataGrid with 20 lines calls the __getitem__ 20 times requesting a one item slice instead of calling it once requesting a 20 item slice.
This will make my attempt to use the DataGrid with large datasets eiter very slow (one round trip per row) or very complex (the DataGridSlicer client side class would need to retrieve rows in larger chunks and manage a cache of rows), but I still would like to give it a try.
If you set a Data Grid’s items to be a SearchIterator, it will only load the next few pages. When the Data Grid is asked for a page it hasn’t got, it makes a request for the next few pages. So the Data Grid pagination is designed to be scalable.
Why Your DataGridSlicer makes one request per row
In your example, you are setting
self.repeating_panel_1.items = DataGridSlicer()
And your DataGridSlicer only has a __getitem__ and a __len__. The Repeating Panel is designed to take an iterator. So it calls __getitem__ for each row in the Data Grid’s current page. Since your __getitem__ makes a server function call, that means a server round-trip for each row.
SearchIterators do not make one call per __getitem__ (or __iter__, which is used preferentially when iterating). They load several pages of data at once, as explained above.
Transforming data
If you want to apply a transformation to your data somewhere between the database and the Data Grid, the scalable way to do that depends on the transformation you want to apply. I might be able to help you figure out how to apply your transformation scalably - what exactly is it you’re trying to do?
I guessed it would take a sliceable object rather than an iterator because (1) there is a button to go to the previous page and (2) app_tables.my_table.search()[10] works. I guessed wrong.
My new guess is that the SearchIterator does implement __getitem__ and __len__, but while the __len__ does a good job at quickly returning the number of items, the __getitem__ seems to always restart the iteration from the first item. I noticed that __getitem__ is O(n), not really scalable. The last page button is unusable with tables with thousands of items.
With my DataGridSlicer class I was trying to make the Data Grid pagination O(1) by using the OFFSET and LIMIT and retrieving only the rows used on each page. My wrong assumption was that the Data Grid would know what page to show (it has pagination buttons), it would create a small list with items[i,i+n] and pass it to its repeating container to show the current page.
The problem is with the fact that Data Grids support nesting - if you put a bunch of Data Row Panels within a single row in the outer Repeating Panel programatically, the Data Grid has no way of knowing how many pages there are until it has constructed every single page. So if you wanted to select an arbitrary page somewhere in the middle of the data, it cannot know which data to select, because it doesn’t know in advance how many nested rows there are until it actually constructs the nested Data Grid.
Loading arbirary pages from large datasets scalably is definitely something Data Grids need to be able to do, so we’re working on an update to Data Grids that makes inclusion of nested Data Row Panels in the row count an opt-in feature.