Pagination of SQL Queries

[PaulMcF1987]

Has anyone managed to add pagination into their SQL Query within budybase?

I have tried a few things and nothing seems to work.

Using OFFSET and FETCH, I get the error
Query Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY' at line 5

Using Offset and Limit I get the error
Query Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10 offset 10; LIMIT 10 offset 20' at line 6

Is there a better wat to achieve pagination

Or... In regards to this issue - #2585
Has this been fully resolved...
Is there any way to filter with conditions AND, OR etc?

[melohagan]

Hi @PaulMcF1987

If you are getting this type of message You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near then it indicates that there is an issue with the MySQL query - it's invalid in MySQL Workbench as well.

To the point of pagination, when using a SQL datasource in a Data Provider and checking the pagination checkbox, it will do the pagination for you.
But I'm assuming in this case you must use a SQL query.

I see you raised an bug here: #6226

It was recommended that you try using limit/offset, however I don't know that this would allow you to use the pagination option in a table component.
That integration would need to be built out similar to how it is for REST pagination: https://docs.budibase.com/docs/rest-pagination

You may need to build some custom view with buttons for next page/previous page, and then pass through binding values for the limit and offset, when you get the SQL syntax correct.

[PaulMcF1987]

@melohagan
I have reverted back to using MYSQL as opposed to Budibase DB
I have tried using HeidiSQL to create a view to filter the information that I need, similar to what can be achieved using Budibase DB

The issue I am now coming up against is that Budibase wont list the table as it does not contain a primary key. To my knowledge though, it is not possible to create a view with a primary key
Is there a work around to this?
No primary key constraint found for the following: View1

Also, the documentation in the above link does not resemble what I see when I create a query.
I am assuming that means that Selfhosted Budibase is quite different to the hosted version?

[PaulMcF1987]

@melohagan
I notice I have just asked that same as what has been asked here - #6216

The problem with creating a query inside Budibase is that it then leads me to the issue I have mentioned above regarding pagination.
I thought creating a veiw via HeidiSQL and then running that view inside of Budibase might resolve this issue but it adds a different issue in place as the view is unusable.

Is there something else that I can try?

[melohagan]

I'm not familiar with HeidiSQL, so I'm not going to be of much use there.

As I said, without the pagination built into the tables, you would need to create a custom 'table view' of sorts in the designer.
You can use Repeaters for each of the rows, with next/previous buttons passing the indices needed for your offset.

[PaulMcF1987]

@melohagan
HeidiSQL is a database management tool, similar to MYSQL Workbench

Are there any examples of where this has been done using an external DB? As per the question, I have tried this but I am struggling to get the pagination.