GroupByPagination Trait - Laravel Eloquent Custom Pagination Example

1-README.md

GroupByPagination Trait

An example of custom pagination for Laravel Eloquent. In this scenario we have a model with a column (e.g. date) which we want to use to group records with for pagination but at the same time we still want to work with separate entities in the view.

The trait replaces QueryBuilder with a custom class which overrides forPage method used for pagination. It normally adds OFFSET and LIMIT parameters to the query which limit the number of results for a particular page. In our case though we are quering the datasource first for values of the defined groups and getting the minimum and maximum values to use them in the pagination query instead of LIMIT and OFFSET. We are also removing defined groups for the final query, so the pagination will use grouped dataset while results will be individual records.

After setting up the trait in a model you simply call paginate(). If no groups are defined the original forPage() method will be used instead.

Example:

class Stats extends BaseModel
{
    use GroupByPagination;
    protected $dates = [
        'date'
    ];
    protected $perPage = 7;
}

Stats::groupBy('date')->orderByDesc('date')->paginate();

GroupByPagination.php

<?php

namespace App\Traits;

use Illuminate\Database\Query\Builder;

trait GroupByPagination
{
    /**
     * Get a new query builder instance for the connection.
     *
     * @return \Illuminate\Database\Query\Builder
     */
    protected function newBaseQueryBuilder()
    {
        $connection = $this->getConnection();

        return new GroupByPaginationBuilder(
            $connection, $connection->getQueryGrammar(), $connection->getPostProcessor()
        );
    }
}

class GroupByPaginationBuilder extends Builder
{
    /**
     * Create a query for a given page.
     *
     * @param  int  $page
     * @param  int  $perPage
     * @return \Illuminate\Database\Query\Builder|static
     */
    public function forPage($page, $perPage = 15)
    {
        if($this->groups)
        {
            $records = $this->cloneWithout(['columns', 'limit', 'offset'])
                ->cloneWithoutBindings(['select'])
                ->select($this->groups)
                ->offset(($page - 1) * $perPage)
                ->limit($perPage)
                ->get();

            if($records->isNotEmpty()) {
                foreach ($this->groups as $group) {
                    $values = $records->pluck($group);
                    $this->whereBetween($group, [$values->min(), $values->max()]);
                }

                $this->groups = null;

                return $this;
            }
        }

        return parent::forPage($page, $perPage);
    }
}

Thanks! Work for me with added $clone->select($this->groups); to runPaginationCountQuery:

protected function runPaginationCountQuery($columns = ['*'])
    {
        if ($this->groups || $this->havings) {
            $clone = $this->cloneForPaginationCount();

            if (is_null($clone->columns) && ! empty($this->joins)) {
                $clone->select($this->from.'.*');
            } else {
                $clone->select($this->groups);
            }

            return $this->newQuery()
                ->from(new Expression('('.$clone->toSql().') as '.$this->grammar->wrap('aggregate_table')))
                ->mergeBindings($clone)
                ->setAggregate('count', $this->withoutSelectAliases($columns))
                ->get()->all();
        }

        $without = $this->unions ? ['orders', 'limit', 'offset'] : ['columns', 'orders', 'limit', 'offset'];

        return $this->cloneWithout($without)
            ->cloneWithoutBindings($this->unions ? ['order'] : ['select', 'order'])
            ->setAggregate('count', $this->withoutSelectAliases($columns))
            ->get()->all();
    }