Pagination on Custom MySQL Query : any ideas ?

[poirazis]

Is it possible (maybe in a hackish way) to paginate the results of a custom query from MySQL ?

[R2bEEaton]

Hey @poirazis!

What do you mean by this? I have a custom MSSQL query which I use in a data provider and I'm able to paginate it just fine using the built-in paginate option limited to 50 rows per page. I didn't have to change my query at all.

[poirazis]

I have a custom MySql query and when I bind it to the data provider under paginate it says it's for data coming from tables only. !

IMG_20220816_220922.jpg

[R2bEEaton]

I have a custom MySql query and when I bind it to the data provider under paginate it says it's for data coming from tables only. !

IMG_20220816_220922.jpg

Hmm, what happens when you try checking the box anyway?

[poirazis]

Nothing, checks and unchecks but no pagination controls appear in the Data Provider 🤷

[poirazis]

Hey @melohagan sorry to have to tag you. Any ideas on this ? Is this the expected behavior or should i raise a bug ?

[melohagan]

There's a bug already open: #3974

[melohagan]

Hi @poirazis

Paginating the results from a custom query in MySQL is fairly straight forward.

Data

Fetch your table. In my case it's a table of people names A-Z.
Next create a query with a binding for LIMIT and OFFSET. I'm using a basic select * from ... but this should work for more complicated queries as well.

Design

Add a Form, Data Provider (Paginate query), Table.
Under the table add a container to hold a back and next button, with a number field in between.

Give the page number field a name, and default value of 1.
Click to cog icon on the data provider, and add a JavaScript binding for the offset.

return ($("Form.Fields.Page")-1) * 5

multiply by your limit, in this case 5

Add conditional UI to the back button to prevent negative offsets.

Note: it acts as a Less than or equal

Define actions on the back button:

Define actions on the next button:

Hide the next button if the number of table rows is less than or equal to the limit minus one. You may want to be smarter here and have a second query to "look ahead" instead.

Result

[melohagan]

It's not something I'm too familiar with, but looking through the code I can see this flow:

• DataProvider.svelte calls fetchData.update
• fetchData.js creates a new Fetch of type QueryFetch
• QueryFetch.js extends DataFetch.js
• DataFetch.js update method calls getInitialData which currently determineFeatureFlags, which looks as follows:

So if I was working on this, the next step would be to determine what happens if I simply set supportsPagination to true. Presumably there's a reason it's not so simple.

Another thing to look at is mysql.ts which extends sql.ts

The read method in there uses knex (you can find that library on GitHub). You can see this method handles pagination, but it only works for "datasource plus" i.e. SQL table datasources (not custom queries).

The custom query path however goes through the internalQuery method of mysql.ts.

It would need a bit of a play around for myself as well, but hopefully that's a little bit of a starting point for you!

[ashik-entym]

This would be a nice feature to have , I current have a use case where I join 3 tables and display it. There is potential for this select query to return large numbers so pagination would be nice. Is there any way to use mysql views instead for this purpose ?

[daraul]

I'm in a situation where I need to add sorting to a table that I've already applied this workaround to -- is my only recourse to get this working to rebuild the entire table by hand?

[melohagan]

Hey @daraul would it not be a similar process, but including the ORDER BY statement in your query?

[R2bEEaton]

Any updates on this? A much-needed feature. Both allowing SQL Views as a workaround or just allowing custom queries to be paginated would go a long way.