CODEWITHSUNDEEP
laravellaravel-4laravel-pagination
Anam
Anam

Reputation: 12169

Laravel pagination not working with group by clause

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

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?

Upvotes: 9

Views: 15693

Answers (5)

user8838201
user8838201

Reputation: 1

This works if you want to group by and paginate.

$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')
    ->paginate(5);

Upvotes: -2

Rafik Farhad
Rafik Farhad

Reputation: 1202

I know it is an old question, by I am sharing my solution for future reference.

I managed to write a function based on this link which does the heavy job of determining the pagination of a complex query. Just pass the 'QueryBuilder' and it will return the paginated object/collection.

Additionally, this procedure can track and maintain the other parameters except for 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;
    }

Upvotes: 1

Atiqur
Atiqur

Reputation: 4022

  1. create a database view namedvw_anything. MySql query will be like

    create view vw_anything as 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;

  2. Now create a new model named UserSubModel for this view, protected $table = 'vw_anything';

  3. Now your paginate query will be like UserSubModel::orderBy('subjects.updated_at', 'desc')->paginate(25);

.

To answer this questioin Laravel Pagination group by year and month only

View query will be :

create view vw_anything as select gallery.*, DATE_FORMAT(created_at, "%Y-%m") as tanggal,count(created_at) as jumlah from gallery group by tanggal;

Let you model is VwModel then your paginate query will be 

VwModel::where('type','Foto')->orderBy('tanggal','desc')->paginate(2);

Upvotes: -1

naT erraT
naT erraT

Reputation: 541

This works for me in 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'))

Upvotes: -1

Mahendran Kannan
Mahendran Kannan

Reputation: 161

Check the documentation https://laravel.com/docs/5.2/pagination

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.

Upvotes: 12

Related Questions