It seems Laravel pagination deos 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?
LIMIT
s when it can, but as soon as a query has aGROUP BY
it goes into a mode whereby it has to get all results and do the pagination manually in PHP usingarray_slice
. I was once looking around the paginator classes and actually thought it was a bug that the paginator didn't useLIMIT
s but then realised that was only when it's aGROUP 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! – alexrussell