Advice - best way to filter table based on sql query

How do I do X?
1

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.

2

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;
3

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() }}

4

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.

5

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

6

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” }}

7

This is a bug.
We are tracking it here:

8

and it’s already closed. impressive.

9

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

10

Is this bug fixed rolled out? I am on version 1.6.8 and I still get this bug.

In the mean time what is the actual work around, because I am this close to becoming bald!