Implementing Server-Side Pagination Using Postgresql in Appsmith (Part 3 - Searching)

In this third part of the series on how to implement server-side pagination using PostgreSQL, the focus is on implementing server-side search. To achieve this, the client-side search option in the table widget must be disabled. Then, the Query_getPosts query is updated to include search parameters from the search input on the table. The title column in the table is used as the basic search index. The query evaluates to 

SELECT * FROM posts 
WHERE title ILIKE '%{{Table1.searchText}}%' 
LIMIT {{Table1.pageSize}} OFFSET {{(Table1.pageNo - 1) * Table1.pageSize}};

 when the search box is not empty. 

If the search box is empty, it evaluates to 

SELECT * FROM posts WHERE title ILIKE '%%' LIMIT {{Table1.pageSize}} OFFSET {{(Table1.pageNo - 1) * Table1.pageSize}};

The onSearchTextChanged property of the table widget is then set to run the Query_getPosts query anytime a user enters something in the search input, with the client-side search option disabled. The table will automatically return the appropriate data whenever the user inputs anything into the search input on the Table widget, searching based on the title column.

It should be noted that this query only searches based on the title column. Further changes are required if the table should be searched based on a separate field. The next part of the series will focus on implementing server-side filtering. Though the filtering that will be implemented is not as sophisticated as the default filtering functionality provided by Appsmith when using client-side pagination, it will serve as a foundation for building something more sophisticated in the future.