How to implement pagination in custom query in backand

Question

I want to implement pagination in my custom query like it is done in the provided queries. I want to provide the number of rows and the page.

I tried the following but it seems that this is not possible:

SET @start = ('{{rows}}' * '{{page}}');
SELECT `table`.`text`, FROM `table`   
order by `table`.`text` desc
limit @start, {{rows}}

And this not either

...
limit {{rows * page}}, {{rows}}

score 2 · Accepted Answer · edited May 23 '17 at 12:23

Try this

        SET @records := {{rows}};
        PREPARE stmt FROM
        " SELECT    *
          FROM      Users
          LIMIT     ?, ?";

        SET @offset := @records * ({{page}} -1 );
        EXECUTE stmt USING @offset,@records;

Here is Back& docs on pagination http://docs.backand.com/en/latest/what_would_you_like_to_do/retrieve_data/index.html#pagination

You are getting this error because MySQL requires numeric constants for that LIMIT syntax.

see answer here Use LIMIT to paginate results in MySQL query

How to implement pagination in custom query in backand

1 Answers1