Implementing Server-side Pagination using PostgreSQL in Appsmith (Part 3 - Searching)

How do I do X?
1

Welcome to part three of the series on how to Implement server-side pagination using PostgreSQL. If you’re just stumbling on this post for the first time, you can locate the first part here and the second part here

In this part, we’re going to be focusing on how to implement search in addition to our earlier implementation of server-side pagination.

Implementing server-side search

To implement server-side search, we have to first disable client-side search in our table widget by locating this option in our table settings.

  1. Go to the Search & Filters section of your table widget settings and disable the Client Side Search option:
    3.1_disable_client_search

  2. Update your Query_getPosts query to include the search parameters from the search input on the table like so:
    SELECT * FROM posts WHERE title ILIKE '%{{Table1.searchText ?? ''}}%' LIMIT {{Table1.pageSize}} OFFSET {{(Table1.pageNo - 1) * Table1.pageSize}}; .
    I’m sure you’re wondering what all these extra parameters mean. First of all, we updated the query to use the ‘title’ column in our table as the basic search index and we set this value to either what is entered into the search input or nothing (in the case where the user doesn’t enter anything).
    So, when the user enters something into the search box on the Table widget, our query evaluates to the following:
    SELECT * FROM posts WHERE title ILIKE '%{{Table1.searchText}}%' LIMIT {{Table1.pageSize}} OFFSET {{(Table1.pageNo - 1) * Table1.pageSize}}; . However, if the search box is empty, our query evaluates to the following:
    SELECT * FROM posts WHERE title ILIKE '%%' LIMIT {{Table1.pageSize}} OFFSET {{(Table1.pageNo - 1) * Table1.pageSize}}; which will basically select everything.

  3. Now, all we have to do is set our table to run the Query_getPosts query anytime our users enter something in the search input. To do this, locate the onSearchTextChanged property of the table widget and set it to run our Query_getPosts query like so:

    3.2_run_query_on_search_text_changed
    3.2_run_query_on_search_text_changed610×542 32.9 KB

    (make sure you have the client-side search option disabled as suggested earlier).

Now, our table should automatically return the appropriate data whenever our users enter something into the search input on the Table widget.
One more thing you should note is that this query only searches based on the title column. You should change this if you want your table to search based on a separate field.

What’s next?

One more thing we have to do is to implement server-side filtering. Filtering allows us to filter our data based on certain fields, values, and conditions. Although, we won’t be implementing something as sophisticated as the default filtering functionality provided by Appsmith when using client-side pagination, however, we’ll do something that will serve as a foundation for you if you want to build something as sophisticated as that.

In the next part of this series, we’ll be doing just that.

See you in the next part!!!

You can find the complete application here - Appsmith