Hi, I have one dataset that must be loaded from the database initially aka can't be hard-coded in the app. Then it stays always the same during whole app runtime. This dataset is used often and in SEVERAL in dropdownlists and with different filters. Calling SQL query with different filter aka WHERE condition, is ineffective and also has an impact on user experience.
Is there any way how I can use this query or temporary state with different filters in different DropDownLists? One dropdownlist has filter for "liquid", the second has a filter for "weight" and so on.
It's important that one filtering doesn't impact dataset on another DropDownList filter.
The sourcing dataset must stay the same. I assume using a temporary state for multiusage, but if the query result dataset can be used, it will save several steps of creating temporary state.
I've attached screenshots and a sample application.
Temporary State Dataset in DropDownList.json
Have you explored using Query JSON with SQL to run filters on your data once you've imported it?
Hi @Kabirdas, I've checked the suggested article. However, I still have challenges.
- The resultset from the SQL query is stored in data as columns, not as records. Therefore, query JSON with SQL doesn't work because the WHERE clause can't find the second column filtered column. Please have a look at the getSelectedSizeGroup query in the attached app.
- The challenge is like chaining dropdown lists (DDLS). The second DDLS source dataset depends on the first DDLS. Please have a look at
Temporary State Dataset in DropDownList (1).json (23.8 KB)
You might try using
formatDataAsArray helper function to do the conversion. It's inverse,
formatDataAsObject may be helpful as well in the transformer on your Query JSON with SQL query. Does that work?
Hey, @Kabirdas formatDataAsArray will help. However, my whole exercise here is to avoid multiple DB calls to get the same dataset just to provide its subsets into dropdownlists. Do you know about filtering directly on the dropdownlist's source attribute, please?
Query JSON with SQL suggested by Kabirdas, does not query your DB. It's a JS library that uses SQL language to filter JS array.
When you look at a SQL query from DB runtime stats you can see backend section.
For Query JSON with SQL, there is no backend section.
Hope that helps.
Hey @Kabirdas I reviewed the suggestion and it works now as I need. formatDataAsArray() and formatDataAsObject() helped
Hey @lamh_bytecode.io, which tool did you use to get these statistics, please ?
You can see that when hover your mouse over the time ran
Note, it's not true indication of how long the query takes particularly when you're in edit mode. If you want to see how long (total time), i recommend to use the timeline in your debug tool while preview or running the app.