The scenario:
- a table that displays data from a sql query.
- a text input that filters data from query.
Question: what is the best place to manage the where clause?
So we can:
- put javascript code in the sql - this is ugly and complicates reading the sql query.
- put javascript in the text input widget onSubmit event - this is difficult to mantain, the props window is small to have large code.
- put code in JSObject and call it on input widget onSubmit event - this is nice, but fails on app start - more below.
So trying to manage the query clause on a JSObject…
We have to create a parameterized slq query, by using {{this.params.whatever}} in the where clause.
We then create a JSObject function that parses inputs from widgets and calls the parameterized sql query.
We then bind the table to the parameterized query.
This should work, but I get errors on app startup because the parameterized sql query tries to run on its own and fails because this.params does not exist…
Your JSObject based solution looks interesting.
What you could do to resolve the error is to provide a fallback value with optional chaining to {{ this.params.whatever }}
It can be provided like this: {{ this?.params?.whatever || "1=1" }}
So, if {{this.params.whatever}} is undefined, it will take “1=1”.
Query:
SELECT * FROM public."users" WHERE {{this?.params?.condition || "1=1"}} LIMIT 10;becomes SELECT * FROM public."users" WHERE 1=1 LIMIT 10; on initial load.
If you find this ugly, you can create another JSObject function to encapsulate this logic as well.
So, query becomes:
SELECT * FROM public."users" WHERE {{JSObject1.myNewFn(this?.params?.condition)}} LIMIT 10;