Laravel 分页不适用于 group by 子句

本文介绍了Laravel 分页不适用于 group by 子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

似乎 Laravel 分页无法与 group by 子句一起正常工作.例如:

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);

制作

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

请注意，查询中没有 limit 子句.

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

如果查询中没有 group by 子句，则工作正常:

$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);

产生了以下查询:

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?

推荐答案

查看文档laravel/docs/5.2/pagination

目前，使用 groupBy 语句的分页操作不能由 Laravel 高效执行.如果您需要使用 groupBy分页结果集，建议查询数据库并手动创建分页器.

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.