How can I achieve the same effect as ROW_NUMBER when using pagination?

I want to add count of rows per group of one of the columns. It works fine with regular queries, but not with pagination because each page starts the count from 0.

For example, I have the following table:

id | category_id | item_id | created_at
---|-------------|---------|-----------
 7 |      11     |   106   | 2024-05-06
 6 |      3      |   102   | 2024-05-06
 5 |      11     |   101   | 2024-05-05
 4 |      9      |   98    | 2024-05-04
 3 |      3      |   97    | 2024-05-03
 2 |      1      |   91    | 2024-05-02
 1 |      11     |   89    | 2024-05-01

And I want to count what order the item_id is in its category by the creation date.

For that I'm using the following query:

DB::('table')->select(
    table.*,
    DB::raw('ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY created_at) as order')
)->get();

Which is the same as the following raw query:

SELECT table.*, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY created_at) as order 
FROM table

And it results in:

id | category_id | item_id | created_at | order
---|-------------|---------|------------|-------
 7 |      11     |   106   | 2024-05-06 |    3
 6 |      3      |   102   | 2024-05-06 |    2
 5 |      11     |   101   | 2024-05-05 |    2
 4 |      9      |   98    | 2024-05-04 |    1
 3 |      3      |   97    | 2024-05-03 |    1
 2 |      1      |   91    | 2024-05-02 |    1  
 1 |      11     |   89    | 2024-05-01 |    1

But it won't work with pagination because each page has its own subset and the count starts from another row, the if for example I paginate the above table every 4 results, and end up with 2 pages, then page number 2 would result in the following:

id | category_id | item_id | created_at | order
---|-------------|---------|------------|-------
 7 |      11     |   106   | 2024-05-06 |    2
 6 |      3      |   102   | 2024-05-06 |    1
 5 |      11     |   101   | 2024-05-05 |    1

But I want it to be

id | category_id | item_id | created_at | order
---|-------------|---------|------------|-------
 7 |      11     |   106   | 2024-05-06 |    3
 6 |      3      |   102   | 2024-05-06 |    2
 5 |      11     |   101   | 2024-05-05 |    2

How can I do it?

Solution

Since pagination using the ->paginate() method uses LIMIT and OFFSET in the background, I don't think you can.

However, if you have no qualms about getting all the results and THEN returning only a subset of them with pagination, then you should be able to do it.

Caching the query's results could help as well.

use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Support\Facades\Cache;

// query
$ttl = 3600; // 60 * 60 seconds (1 hour)
$results = Cache::remember('query-results', $ttl, function () {
    return DB::('table')
        ->select(
            table.*,
            DB::raw('ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY created_at) as order')
        )
        ->get();
});
// pagination
$page = request()->query('page', 1); // using the ?page= query parameter to get the current page
$perPage = 4;
$paginator = new LengthAwarePaginator(
    $results->forPage($page, $perPage)->values(),
    $results->count(),
    $perPage,
    $page,
);
  
// return response()->json(['data' => $paginator]);