首页 文章

Laravel分页不适用于group by子句

提问于
浏览
5

似乎Laravel分页deos与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')
            ->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

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

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

有谁知道我该如何解决这个问题?

3 回答

  • 0

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

    目前,Laravel无法有效执行使用groupBy语句的分页操作 . 如果需要将groupBy与分页结果集一起使用,建议您查询数据库并手动创建分页器 .

  • 6

    这在laravel 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'))
    
  • 0

    我认为如果你想分组和分页,这是有效的 .

    $code = DB::table('sources')
    ->select(DB::raw('sources.id_code,sources.title,avg(point) point'))
    ->join('rating','sources.id_code','rating.id_code')
    ->groupBy('sources.id_code')
    ->groupBy('sources.title')
    ->groupBy('sources.language')
    ->groupBy('sources.visited')
    ->groupBy('sources.')
    ->paginate(5);
    

相关问题