I need to return a client_readable_view to the client which has been prefiltered on a subset of the linked column rows.
This does not work and raises internal server error:
demand_rows = app_tables.demand.search(
q.fetch_only(),
FORECAST=q.not_(None)
)
master_row_view = app_tables.master_data.client_readable(
q.only_cols(
"PRODUCT_ID",
"LOCATION",
"linked_demand"
),
linked_demand=q.any_of(*demand_rows),
)
# I would like to return the master_row_view here to the client
# and perform searches, however experience internal server error
# whenever I perform any search on the view
res = master_row_view.search(
q.fetch_only(),
)
The query below works but the master view has not been prefiltered for the demand rows that I’m interested in. I have to filter on the demand rows everytime I do a search on the view.
demand_rows = app_tables.demand.search(
q.fetch_only(),
FORECAST=q.not_(None),
)
master_row_view = app_tables.master_data.client_readable(
q.only_cols(
"PRODUCT_ID",
"LOCATION",
"linked_demand"
)
)
# When I place the linked_demand filter within the search, as shown below,
# I get the results I expect. However, it would be great to pass the prefiltered
# master_view to the client and perform operations on the prefiltered view
res = master_row_view.search(
q.fetch_only(),
linked_demand=q.any_of(*demand_rows)
)
The only difference between the scenarios above is where I place the linked_demand=q.any_of(*demand_rows).
It is possible to filter a client_readable_view, but doesn’t seem to work when I try and filter on linked rows. Is this the case or am I missing something?
That speeds up the query by not fetching any data for the demand rows. In essence it only fetches the references to the rows which I want to use to filter on the master table.
The reason I used it in the master table as well is just to speed up the query for now, because in this example I’m not worried about actual values in the result, merely want to show the behavior.
I just checked, and it looks like this error isn’t in fact triggered by searching by linkable row; it’s a “payload too large” error which occurs sometimes when something is trying to return too much data at once (usually this is because you’re returning lots of data from the database, eg huge SimpleObject columns or lots and lots of linked rows). However, the code you’re showing here seems to be fetching from the view with q.fetch_only(), which seems unlikely to trigger that…but this is also a simplified version so I want to confirm where this error occurs, and whether it actually corresponds to any of the sample code you’ve provided here!
Just a thought: How big is demand_rows? If that’s a large number of rows, that might do it! View restrictions aren’t really designed to contain huge amounts of data.
Okay, I can confirm it is a memory issue. I have created a dummy app to show the behaviour.
To replicate, in a server console run:
from . import ServerModule1
ServerModule1.setup_example(N=1000)
ServerModule1.filter_on_linked_rows()
If we run the above with N = 1000, it works.
If we run with N = 10,000, it crashes.
So I suppose my question is then, what can we do to return a client_readable view to the front-end which is filtered on a subset of the linked rows? I’d like to return this client_readable view to a datagrid and have it lazy_load on the front-end.
Yep, that sounds plausible! (It’s not a memory issue per se - you’re hitting a different limit there - but putting 10000 rows into a view restriction is not something I’d expect to work!)
Unfortunately, if all you’ve got is “does it have a linked row in this [large] set?”, that means client-readable views aren’t really an option - they can’t express that limitation. My advice would be to move a bit more of the logic to the server side, rather than using a view.
I never return row or view or iterator objects to the client. I only return what the client needs, so I’m sure there is only one round trip and all and only the data I need is transferred.
If you look at the AutoScroll custom component you see how I manage the lazy loading. (In this simple example I break the rule and do return a list of row objects rather than dictionaries, but yeah, this is just a simple example).
The downside: is not automatic and you need to take care of everything (not really complex).
The upside: you have the control and you are 100% sure you don’t get sneaky round trips slowing you down, or other problems with large data.