Category: How do I do X?
Updated

This solution is summarized from an archived support forum post. This information may have changed. If you notice an error, please let us know in Discord.

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

Issue

In this article, I am trying to implement server-side search on a table widget using PostgreSQL. I need to disable client-side search and update my Query_getPosts query to include search parameters from the search input on the table. By setting the table to run the Query_getPosts query anytime a user enters something in the search input, the table should automatically return the appropriate data. I also need to note that this query only searches based on the title column. The next step will be to implement server-side filtering.

Resolution

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.