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?

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.