Laravel v4.2 GroupBy and Paginate Problem with Eager Loading - Fixed

Disclaimer: This article only applies to Laravel v4.2, as we are backporting bugs in the framework to fix them in older projects.

If you have been using Laravel since 2013 and later, you may have been using it since the popular release of Laravel version 4.2 on June 1, 2014.

I've been using Laravel since 2012, during its early stages when it was establishing itself as a prominent framework, alongside other frameworks like CodeIgniter. I played around with CodeIgniter for a while and saw that people were loving Laravel, so I shifted to it. Since then, I have witnessed everything that has come to life from Laravel and have enjoyed developing robust Laravel apps and APIs.

In this article, I'm going to take you through a well-known bug in the framework that many people have encountered and reported for a fix. However, Laravel has soared in popularity, and support for version 4.2 has reached its end of life, leading many to stop using this older version of the framework.

Despite this, I've noticed that many businesses do not upgrade their projects to the latest Laravel version over time because it costs money and time, and sometimes the older version continues to work well for them under the hood.

If you have faced this issue, you might have explored the Laravel repository on GitHub and come across the issues that people have created for the bug, such as: "groupBy and paginate problem with Eager Loading" (#5063, #5072). If you look through these issues, you will see that Taylor Otwell, the author of Laravel, has asked people to submit a pull request (PR) for its fix. Unfortunately, no one in the community has managed to address it, as many may not have noticed those issues.

What is the issue, and how to re-create it?

    $products = Product::query()
        ->enabled()
        ->with(['variants'])
        ->groupBy('product.id')
        ->paginate(10);

The above Laravel Eloquent query builder generates the following SQL query:

select * from `product` where `product`.`deleted_at` is null and `product`.`status` = ? group by `product`.`id`

select * from `product_variant` where `product_variant`.`deleted_at` is null and `product_variant`.`product_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ....many more ....)

The problem is that the SQL generated for Eager Loading executes a query that retrieves all results from the product variants table instead of just the specific number of results needed for pagination of the selected products.

So how to solve it?

As I mentioned earlier in the article, Laravel v4.2 has already reached its end of life for support, so do not expect any fixes to be released for the framework.

So, if by any chance you have older projects running on Laravel v4.2 like I do, and need to work on them frequently to add features and fix bugs for clients, you can consider the solution below, which has worked perfectly for me.

<?php

namespace App\Models;

use Illuminate\Support\Facades\DB;

/**
 * Class BaseModel
 * @package App\Models
 */
class BaseModel extends \Eloquent
{
    /**
     * @param  \Illuminate\Database\Eloquent\Builder $query
     * @param  int $perPage
     * @param array $columns
     */
    public function scopeCustomPaginate($query, $perPage = null, $columns = ['*'])
    {
        $perPage = $perPage ?: $this->getPerPage();

        $eloquent = $query->getQuery();

        $connection = $eloquent->getConnection();

        $paginator = $eloquent->getConnection()->getPaginator();

        $total = $connection->table(DB::raw("({$query->toSql()}) as subquery"))
            ->select(DB::raw('count(*) as count'))
            ->setBindings($query->getBindings())
            ->first()
            ->count;

        $currentPage = $paginator->getCurrentPage($total);

        $results = $query->forPage($currentPage, $perPage)->get($columns);

        $stack = [];

        $results->each(function ($model) use (&$stack) {
            $stack[] = $model;
        });

        return $paginator->make($stack, $total, $perPage);
    }
}

I've added a new class that will later be extended by my Product model.

<?php

namespace App\Models;

/**
 * Class Product
 * @package App\Models
 */
class Product extends BaseModel
{
    ....
}

It is not always necessary to create a class and extend it; you can also use a trait in the model. However, my structure has some generic functions in the base model that I need to call in all other models in the project, so I'm keeping them in the class.

Now, see the updated query below.

    $products = Product::query()
        ->enabled()
        ->with(['variants'])
        ->groupBy('product.id')
        ->customPaginate(10)
    ;

The updated code utilizes the new customPaginate() method that was introduced as a query scope in the BaseModel, allowing it to be used anywhere in the other models throughout the project.

So results Before vs After are below.

Before

select * from `product` where `product`.`deleted_at` is null and `product`.`status` = ? group by `product`.`id`

select * from `product_variant` where `product_variant`.`deleted_at` is null and `product_variant`.`product_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ....  many more ...)

After

select * from `product` where `product`.`deleted_at` is null and `product`.`status` = ? group by `product`.`id` limit 10 offset 0

select * from `product_variant` where `product_variant`.`deleted_at` is null and `product_variant`.`product_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

The query above does not actually require groupBy, but I added it to demonstrate the bug.

Thank you for reaching the end of the article. I hope this helps with your project if you have faced this issue.

Happy Coding!