I wanted to add count of rows by grouping by 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();

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.
Is there any way to do that with pagination?