Advice - Best Way to Filter Table Based On Sql Query (Server Side Filtering)

Issue

The scenario:

  • a table that displays data from a SQL query.
  • a text input that filters data from the 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 maintain, the props window is too 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 SQL 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…

Resolution

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, the query becomes:

SELECT * FROM public."users" WHERE {{JSObject1.myNewFn(this?.params?.condition)}} LIMIT 10;