John Davidson

Message:

Let's see, I'm making a mess with the cursor pagination, based on an Id in my case ULID, I want to return an array with the results, next_cursor and prev_cursor.

To obtain the NextCursor is very easy, I only have to add one more to the Limit, that is to say, if I have a limit of 10, I request 11 records and if I get 11 records then the NextCursor is the result 11. But for the PrevCursor the only thing I can think of is to do an additional Query to the one I am already doing. Example:

$limit = 10;



$result = 'SELECT * FROM Table WHERE id <= $cursor ORDER BY id DESC LIMIT $limit+1'



$results    = array_slice($result, 0, $limit);

$nextCursor = array_slice($result, $limit, 1);

And now to get the Prev Cursor, I do as I said before an additional query

$prevCursor = 'SELECT * FROM Table WHERE id > $cursor ORDER BY id ASC LIMIT 1'

That way my API can return the following array to the frontend

return [

  'data'        => $results,

  'next_cursor' => $nextCursor,

  'prev_cursor' => $prevCursor

];

Now I rephrase the same question again, is there any way to do this without having to do additional Mysql query to get the Prev Cursor, I mean in a same Query or in some other way, I don't know, it's the first time I do this, and I'm a bit lost.

Thanks very much!

---

My Answer:
> ' . $lastId . ' ORDER BY id DESC LIMIT ' . ($limit + 1);_

This query will return 11 records starting from the lastId. If there are 11 records returned, then the next cursor will be the id of the 11th record. To get the prev cursor, you can do another query to get the record before the first record in the result set.

php
$prevResult = 'SELECT * FROM Table WHERE id < ' . $firstId . ' ORDER BY id DESC LIMIT 1';


This query will return the record before the first record in the result set. You can use the id of this record as the prev cursor.

So in your code, you can fetch the results, check if there are 11 records, and if there are, set the next cursor to the id of the 11th record. Then, if needed, you can do the additional query to get the prev cursor.

php
if (count($result) > $limit) {
$nextCursor = $result[$limit]['id'];
$prevCursor = // do the additional query to get the prev cursor
}

$response = [
'results' => $result,
'next_cursor' => $nextCursor,
'prev_cursor' => $prevCursor
];

echo json_encode($response);