Laravel分页不适用于group by子句 [英] Laravel pagination not working with group by clause

问题描述

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

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

已生产

            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?

推荐答案

检查文档 https://laravel.com/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.

这篇关于Laravel分页不适用于group by子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！