Nrthugu

If I have this query:

select 

    id,

    @product_rank := IF(@current_category = category_id, @product_rank + 1, 1) AS product_rank,

    @current_category := category_id

from products 

And create a query using Eloquent builder:

Products::from(DB::raw('

    (select 

        id,

        @product_rank := IF(@current_category = category_id, @product_rank + 1, 1) AS product_rank,

        @current_category := category_id

    from products) as q 

'))->where('product_rank', '<=', 10)->paginate(5);

Because Laravel pagination wants to get the total count of all records, it executes an extra query like this:

select count(*) as aggregate from (

    select 

        id,

        @product_rank := IF(@current_category = category_id, @product_rank + 1, 1) AS product_rank,

        @current_category := category_id

    from products 

) q where product_rank <= 10

So @current_category and @product_rank won't re-initialize and the main query returns wrong results.

I wonder if there is way to re-initialize both variables.