Change where clause on a query with a select widget / dynamic query

How do I do X?
1

How can we change data on a widget by choosing options on a select widget and updating the where clause on the query?

2

Appsmith supports dynamic bindings inside the query body; hence it’s super easy to update the clauses or queries, in general, using JS.

Now, say you have a select widget (consider widget name to be Select1) with three options:

  • Users
  • Gender
  • All

To update the data inside the table based on the selected option, first, let’s write a simple query named dynamicQuery:

SELECT {{appsmith.store.table_filter}} FROM public."users" LIMIT 100;

Here, as you can see, we use the moustache bindings to send the attributes (table_filter variable holds attribute in this case) on the query dynamically.

Note: This query is generated on the sample (Postgres) users database available under the Datasources sections.

Now let’s write some JS to handle this condition. For this, you can use JS objects (named as utlis in this example) to organise your code; the following is the function:

export default {
// function start
dyanmicQueryJS: () => {
    if (Select1.selectedOptionValue == 'Users') {
      storeValue('table_filter', 'name, email, id');
      dynamicQuery.run();
    } else if (Select1.selectedOptionValue == 'Gender') {
      storeValue('table_filter', 'name, gender, id');
      dynamicQuery.run();
    } else {
      storeValue('table_filter', '*');
      dynamicQuery.run();
    }
    return 'success';
  },
// function end
}

Here, we use a simple if-else block to store a variable (to use as an identifier) and run the dynamicQuery we’ve created before. The table_filter variable automatically gets overridden after the option is selected.

Lastly, we update the select widget’s onOptionChange property to the following:

{{utils.dyanmicQueryJS()}}

The following question is also discussed on How Do I Do X on Appsmith Session on our community call. Watch it here: How Do I Do X? - YouTube.