"Database Custom Query" VS "Database Paged Query"

Can the “Database Custom Query” be used similar to “Database Paged Query” in order to list 70K+ records without overwhelming the server?

You can set post/get variables to limit the query results in the custom query. Set them when calling the server connect to fetch data.
You will probably have to handle the start and end limits in the UI and page numbers etc too.

I am not sure how it can be exactly replicated as the paged query, but that should be possible too… and that will allow using Bootstrap pagination too. Maybe @patrick can help.

Database Paging Query does handles all the paging logic and parameters by itself.
While when you use Custom SQL Query - then you will have to build it all by yourself.

Custom SQL Query is really only meant for very complex queries that can’t be done with the standard visual query builder.

So if you can use the regular Database Query or Database Pages Query, I will suggest to stick to those as they are already well optimized.

2 Likes

You can check @jcr ;

Not tried this but the paged query uses two parameters for the pagination, OFFSET and LIMIT which are normally passed as $_GET parameters

I suspect you could define these in a custom query something like this:

1 Like

Thanks everyone for your feedback. I have been quite busy and have not had the time to test the suggestions.

Hi @Hyperbytes, thanks for the suggestion. What is missing is how to return the total number of pages and the current page from ServerConnect for the Pagination list to work correctly, any idea of how to do that?

This is what needs to be returned by server connect, in addition to the actual data:
image

The total number of records in a record set can be found with serverconnection.data.queryname.count()

limit and offset should be managed through the state manager which manages the limit and offset $_GET parameters internally within wappler so they always hold the current offset and limit values

image