问Laravel分页不按条款处理组
EN

Stack Overflow用户

提问于 2014-04-14 19:29:08

回答 5查看 14.9K关注 0票数 9

看来，Laravel分页并不能很好地处理逐条分组的问题。例如：

$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子句。

如果查询中没有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

有人知道我该怎么解决这个问题吗？

laravel

laravel-4

laravel-pagination

回答 5

Stack Overflow用户

发布于 2016-01-19 01:57:36

查看文档https://laravel.com/docs/5.2/pagination

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

票数 12

Stack Overflow用户

发布于 2019-12-04 02:40:03

我知道这是一个老问题，通过我分享我的解决方案，供将来参考。

我成功地编写了一个基于此链接的函数，它完成了确定复杂查询分页的繁重工作。只要传递'QueryBuilder‘，它就会返回分页对象/集合。

此外，此过程可以跟踪和维护除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;
    }

票数 1

Stack Overflow用户

发布于 2016-03-29 22:19:05

这对我来说用的是拉拉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'))