Sometimes you want to link to a resource inside a paginated collection by its ID and need to find the correct page, fast.
The Problem
The customer had a comment system in their app. There are multiple resources that can be commented on by users, and these comments have a classical pagination. In Laravel, this would look something like this:
$perPage = 20;
$page = request()->input('page');
$comments = $resource
->comments()
->orderBy('id', 'DESC')
->skip($perPage * ($page - 1))
->take($perPage)
->get();
Of course, the general principle holds true for every programming language and framework.
This code will fetch 20 comments per page, taking the page from the request, showing the most recent comments first. But users are supposed to be notified of replies to their comment, and so we needed a way to link directly to a comment. The issue was, since newer comments will be shown first, the page an individual comment is on will change over time. And because we have potentially thousands of comments, we shouldn’t just load them all into memory and iterate over them.
The Solution
The solution itself turned out to be incredibly easy:
$perPage = 20;
$targetCommentId = request()->input('target');
// get the amount of comments posted after our target
$newerCommentCount = $resource
->comments()
->where('id', '>', $targetCommentId)
->orderBy('id', 'DESC')
->count();
// calculate the page by calculating how many full pages
// are before our target comment:
$page = floor($newerCommentCount / $perPage) + 1;
//...
We simply use SQL to calculate the amount of entries before our desired one, and then calculate the page offset from there.
This works just as well for any other paginated resource.