Laravel pagination not working with group by clause

Question

It seems Laravel pagination does not working properly with group by clause. For example:

$users = Subject::select(DB::raw('subjects.*, count(user_subjects.id) as total_users'))
            ->join('user_subjects', 'user_subjects.subject_id', '=', 'subjects.id')
            ->whereNull('user_subjects.deleted_at')
            ->groupBy('subjects.id')
            ->orderBy('subjects.updated_at', 'desc')
            ->paginate(25);

Produced

select subjects.*, count(user_subjects.id) as total_users 
from `subjects` inner join `user_subjects` on `user_subjects`.`subject_id` = `subjects`.`id` 
where `subjects`.`deleted_at` is null and `user_subjects`.`deleted_at` is null 
group by `subjects`.`id` 
order by `subjects`.`updated_at` desc

note that, there is no limit clause on the query.

Working fine if no group by clause in the query:

$users = Subject::select(DB::raw('subjects.*, count(user_subjects.id) as total_users'))
            ->join('user_subjects', 'user_subjects.subject_id', '=', 'subjects.id')
            ->whereNull('user_subjects.deleted_at')
            ->orderBy('subjects.updated_at', 'desc')
            ->paginate(25);

produced the following query:

select subjects.*, count(user_subjects.id) as total_users from `subjects` 
inner join `user_subjects` on `user_subjects`.`subject_id` = `subjects`.`id`
where `subjects`.`deleted_at` is null and `user_subjects`.`deleted_at` is null 
order by `subjects`.`updated_at` desc 
limit 25 offset 0

does anyone has any idea how can i fix this?

Fredkin

Not sure why but with groupBy the paginator can't handle this in Laravel. It is basically retrieving whole set and on the PHP side slices the result. It's better for you in this case to do it manually or get the count you need differently. – Zita

The above is true - if you look into the code you'll see that the paginator will try to add LIMITs when it can, but as soon as a query has a GROUP BY it goes into a mode whereby it has to get all results and do the pagination manually in PHP using array_slice. I was once looking around the paginator classes and actually thought it was a bug that the paginator didn't use LIMITs but then realised that was only when it's a GROUP BY statement. So yeah - you'll find that the paginator should be working fine, just inefficiently. I just hope you don't have a huge result set! – Cyd

@Cyd thank you. The problem is we have large data set :(. – Fredkin

Then unfortunately doing you own pagination appears to be the only way. – Cyd

Answer 1

Check the documentation https://laravel.com/docs/5.2/pagination

Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

Unreconstructed

Answer 2

I know it is an old question, by I am sharing my solution for future reference.

I managed to write a function based on this link which does the heavy job of determining the pagination of a complex query. Just pass the 'QueryBuilder' and it will return the paginated object/collection.

Additionally, this procedure can track and maintain the other parameters except for page=.

public function mergeQueryPaginate(\Illuminate\Database\Eloquent\Builder $query): \Illuminate\Pagination\LengthAwarePaginator
    {
        $raw_query = $query;
        $totalCount = $raw_query->get()->count();

        $perPage = request('per-page', 10);
        $page = request('page', 1);
        $skip = $perPage * ($page - 1);
        $raw_query = $raw_query->take($perPage)->skip($skip);

        $parameters = request()->getQueryString();
        $parameters = preg_replace('/&page(=[^&]*)?|^page(=[^&]*)?&?/', '', $parameters);
        $path = url(request()->getPathInfo() . '?' . $parameters);

        $rows = $raw_query->get();

        $paginator = new LengthAwarePaginator($rows, $totalCount, $perPage, $page);
        $paginator = $paginator->withPath($path);
        return $paginator;
    }

Summertree

Answer 3

This works for me in laravel 5.2

Select(\DB::RAW("assignment_descendant_child.assignment_descendant_child_id, assignment_descendant_child.assignment_descendant_child_name, COUNT(assignment_descendant.assignment_descendant_id) as xNum"))
            ->leftJoin(
                'assignment_descendant',
                'assignment_descendant.assignment_descendant_child_id',
                '=',
                'assignment_descendant_child.assignment_descendant_child_id'
            )
            ->orderBy('assignment_descendant_child_name')
            ->groupBy('assignment_descendant_child.assignment_descendant_child_id')
            ->paginate(\Config::get('constants.paginate_org_index'))

Avlona

Answer 4

1. create a database view namedvw_anything. MySql query will be like

   create view vw_anything as select subjects.*, count(user_subjects.id) as total_users from subjects inner join user_subjects on user_subjects.subject_id = subjects.id where subjects.deleted_at is null and user_subjects.deleted_at is null group by subjects.id;

2. Now create a new model named UserSubModel for this view, protected $table = 'vw_anything';

3. Now your paginate query will be like UserSubModel::orderBy('subjects.updated_at', 'desc')->paginate(25);

.

To answer this questioin Laravel Pagination group by year and month only

View query will be :

create view vw_anything as select gallery.*, DATE_FORMAT(created_at, "%Y-%m") as tanggal,count(created_at) as jumlah from gallery group by tanggal;

Let you model is VwModel then your paginate query will be

VwModel::where('type','Foto')->orderBy('tanggal','desc')->paginate(2);

Warfield

Answer 5

This works if you want to group by and paginate.

$code = DB::table('sources')
    ->select(DB::raw('sources.id_code,sources.title,avg(point) point'))
    ->join('rating','sources.id_code','rating.id_code')
    ->groupBy('sources.id_code')
    ->groupBy('sources.title')
    ->groupBy('sources.language')
    ->groupBy('sources.visited')
    ->paginate(5);

Incubator