Advice - best way to filter table based on sql query

Hi there.
The scenario:

  • a table that displays data from a sql query.
  • a text input that filters data from query.

Question: 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…

Any advices?

Thanks.

Hi @dncpax,

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;

I’ve been playing with several variations of this though didn’t know of chaining ? like you did. Interesting.

Nevertheless, this is failing but I suspect it’s a js thing and not appsmith:
{{ this?.params?.date || new Date().getFullYear() }}

disregard that… running now.
Thanks very much!
I’ll keep exploring… just not sure what’s the easiest way to organize this kind of stuff so that it’s easier to mantain.

it seems to me that {{this?.params?.var || “default”}} always evaluates to “default”?

so, simple version works… was certain it didn’t, but now it works. people must think I’m nuts…

{{ this.params.var? this.params.var: “1=1” }}

This is a bug.
We are tracking it here:

and it’s already closed. impressive.

Yes. It should be available next week’s release probably.