Advanced Server-side Filtering for Tables in Appsmith
Say you’re a data engineer tasked with creating a user-friendly UI capable of filtering one of your SQL tables. Since you don’t have in-depth frontend knowledge, you turn to low-code/no-code platforms to create attractive UI interfaces in minutes instead of spending days coding everything from scratch. However, you quickly run into missing features that you cannot easily create with the platform’s existing toolkit.
I’ve been there too — and if you’re specifically looking to enable server-side filtering in Appsmith, in this post I’ll tell you how I did it.
We started using Appsmith’s self-hosted version at Starschema to help a client and their business users to navigate through a database migration project. While Appsmith provides a variety of built-in dev tools to streamline quick internal app/tool development, we found that some features can’t be implemented with its no-code interface — at least not yet.
Appsmith is an open-source framework to quickly build any custom business software like admin panels, internal tools, dashboards and more with pre-built UI widgets that connect to any database, GraphQL or REST API, controlling everything with JavaScript.
Let’s start with a realistic scenario where you’d need to do some tweaking to the development framework.
Imagine that you’ve created the default CRUD Application in Appsmith for a Postgres Table holding 10k+ records. The table has multiple columns, but you want to provide an option to users to be able to filter the records by two columns — owner name and migration status — so they can check only the to-be-migrated objects that are related to them. This is something that you can do easily within an Excel spreadsheet or, if you’re familiar, with SQL.
But what if your users are used to the convenience of spreadsheets but there’s also a demand for SQL?
You might have tried to use the built-in filtering option, which has similar capabilities as an Excel filter, only to realize that, because of server-side pagination, it’s not filtering all the matching records, only the paginated ones. If you check the SQL query, it all makes sense.
SELECT * FROM migration_table
ORDER BY “{{data_table.sortOrder.column || ‘id’}}” {{data_table.sortOrder.order || ‘ASC’}}
LIMIT {{data_table.pageSize}}
OFFSET {{(data_table.pageNo — 1) * data_table.pageSize}};Because of the OFFSET parameter, all the data filtered in the UI is limited to paginated values.
“Nevermind,” you say as you turn off the server-side pagination so you can have all the records loaded.
And now your app has crashed.
Turning off server-side pagination might work for tables with only few hundreds of records, but not for tables with thousands or more.
Hence the need to keep server-side pagination turned on.
Adding a WHERE condition to the SQL query
After some Googling, you might come across an Appsmith article by Vihar Kurama, which describes how to add a Select dropdown widget to your page, define its values and how to pass the selected option value to your SQL query, where you can then specify a WHERE condition.
SELECT * FROM migration_tables
WHERE migration_status = {{SelectMigrationStatus.selectedOptionValue}}
ORDER BY “{{data_table.sortOrder.column || ‘id’}}” {{data_table.sortOrder.order || ‘ASC’}}
LIMIT {{data_table.pageSize}}
OFFSET {{(data_table.pageNo — 1) * data_table.pageSize}};Now you’re good to go — you can select the migration status, and it will show only those records.
But how do I populate the Select widget’s options dynamically?
You can define your options manually, or you can populate it by getting the already available, distinct values from the database table:
SELECT distinct migration_status
FROM migration_tables
order by migration_status;You can populate the widget’s options from the SQL results by using a custom JS code
{{ SQLdistinctStatus.data.map(u => ({ label: u.migration_status, value: u.migration_status }))}}
But what if you’re not using a Select widget, but rather a Multi Select one? Let’s recreate it with a Multi Select widget, with the options populated in the same way as above:
Let’s put a check in two of the statuses, then check the SQL statement. One of the cool features of Appsmith, beside syntax highlighting, that it also shows you the evaluated value of your prepared statements.
This doesn’t look good. As you can see,
migration_status = [\“Testing\”,\”Translation\”] doesn’t compile well in SQL.
If only there was a way to turn the above into this:
migration_status in (‘Testing’, ‘Translation’)JS Object saves the day
Create a New JS Object.
You’ll see an editor view pop up, already populated with sample code. Name the object “CustomJS.” You can see that there is a possibility to define some variables, e.g. you can create a synchronous function (myFun1) or an asynchronous function (myFun2).
export default {
myFun1: () => {
//write code here
},
myFun2: async () => {
//use async-await or promises
}
}As we won’t be doing anything network-related — which would require waiting for a response — only string manipulation, we don’t have to create an async function (but, by all means, please reach out and correct me if you find that this is wrong). Replace the above text with the following:
export default {
createWhereFilter: (col_name, keywords) => {
// if there is filter specified, paginate only filtered the records
if (keywords.length > 0) {
let in_statement = "and "+ col_name+" in ('"+keywords.join("','")+"')";
// check if there is a null value in the column
if (keywords.includes(null)) {
// adding null to the end of the in (...) statement
// be aware that "" ~= null
in_statement = in_statement.replace("')","',null)");
}
return in_statement;
}
else {
// if there is no filter specified, paginate all the records
return "";
}
}
}The function’s first parameter is the column name. The second “keywords” parameter must be an array, as we’ll pass the Multi Select widget’s selectedOptionValues array onto it.
To test any parameterized function in Appsmith, you’ll have to do create a helper function to test it. Add this helper function to the code:
testFilter: () => {
return this.createWhereFilter(“migration_status”,[“Translation”,’On Hold’]);
}If you click on the red triangle/run icon, you can test your response.
Looking good! Now, we only have to connect this to our SQL query. Add 1=1 as the first condition before any generated SQL, so if we don’t have any filters turned on, the SQL still complies.
This way, we have a nice, server-side paginated and filtered query.
Filtering by multiple columns
You can also have multiple Multi Select widgets, each capable of filtering distinct values of one of your specific columns. Create a new Multi Select widget same as before, then extend your SQL query with the following:
SELECT * FROM migration_table
where 1=1
{{CustomJS.createWhereFilter(“migration_status”, SelectStatus.selectedOptionValues)}}{{CustomJS.createWhereFilter(“owner_name”, SelectOwner.selectedOptionValues)}}LIMIT {{Table1.pageSize}}
OFFSET {{(Table1.pageNo — 1) * Table1.pageSize}};
Add row counts for better pagination
By default, with pagination turned on, Appsmith doesn’t know how many records of your table holds.
If you do a basic SQL count(*), then pass it to Total Record Count, you can show users the number of all (or filtered) records and the total number of pages.
First, create a new SQL query with a name of RowCount. The following query will return the number of your records, also taking into account your filters.
SELECT count(1) FROM migration_table
where 1=1
{{CustomJS.createWhereFilter("migration_status",SelectStatus.selectedOptionValues)}}{{CustomJS.createWhereFilter("owner_name",SelectOwner.selectedOptionValues)}};Then, add {{RowCount.data[0].count}} to your Table widget’s Total Record Count field. If no filter is specified, you’ll see the count of all records; if filters are turned on, you’ll see the number of filtered records. Pretty cool, right?
And there you have it! With with the smart use of JS Objects, you can enable missing features such as server-side filtering in Appsmith — and if you find a better way to do this, drop me a line — I’d love to hear from you.
