Implementing Server-side Pagination using PostgreSQL in Appsmith (Part 4 - Filtering)

How do I do X?
1

Welcome to the final part of this series. In the first part, we talked about connecting our query to a table widget and displaying our data. In the second part, we went further into implementing server-side pagination into this table, and in the third part, we added searching to it.

In this part, we’ll be adding server-side filtering into the application. Let’s get into it!!!

Implementing server-side filtering

Adding server-side filtering to our existing application is rather very simple. However, we have to understand what needs to be done in order to be able to do it.

  1. We need to provide a way for our users to select specific fields, for this purpose, we’ll be making use of the select widget.
  2. For the field selected, we need to provide our users with a way of providing a filter value. For this, we’ll be using the input widget
  3. Lastly, we need to update our query to use both widgets as specified above.

Let’s dive right in!

  1. Drag a select widget into your canvas and position it wherever you want, update the data to display the columns in your table like so:

    4.1_set_filter_select
    4.1_set_filter_select2179×695 87.9 KB

    In my case, I only want my users to be able to filter by the Title and Body columns

  2. Set the onOptionChange property of the select widget and the onTextChanged property of the input widget to re-run the Query_getPosts query.

  3. In the Table widget settings, disable the Allow Filtering option to remove the default filtering functionality.

  4. Update your query to use the values from our select widget and the input widget like so:
    SELECT * FROM posts WHERE {{W_tableFilter.selectedOptionValue ?? 'title'}} ILIKE '{{'%' + `${W_tableFilter.selectedOptionValue ? W_tableFilterInput.text : Table1.searchText}` + '%'}}' LIMIT {{Table1.pageSize}} OFFSET {{(Table1.pageNo - 1) * Table1.pageSize}};

W_tableFilter.selectedOptionValue refers to the currently selected field of our select widget and W_tableFilterInput.text refers to the input of the input widget.

With this update, you should be able to filter by title and body without any issues.

That’s it for server-side filtering using PostgreSQL!

You can find the completed application here - Appsmith