CODEWITHSUNDEEP
laravel
Emerson Nunes
Emerson Nunes

Reputation: 456

Laravel groupBy with pagination

I'm trying to use the queryBuilder to get all the results from one table, paginated by 15 grouped by one field.

$data['invoices'] = InvoiceModel::selectRaw("*")
    ->groupBy('serie')
    ->paginate(15);

Laravel throws the following error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'projetoservidor.vendas_195295269.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select count(*) as aggregate from (select * from vendas_195295269 group by serie) as aggregate_table)

What should I do?

Upvotes: 5

Views: 12567

Answers (6)

PillFall
PillFall

Reputation: 164

Eloquent Relationship

Assuming Invoice n..1 Serie

If serie is an Eloquent Relationship you can group by paginate using the relationship model as the pagination target.

Serie::with([
    'invoices',
])
    ->paginate();

This can also give you the possibility to filter by serie or invoice

If filtering by invoice for example

Serie::whereIn('id', Invoice::where($condition)->select(['serie_id']))
    ->with([
        'invoices' => function ($query) use ($condition) {
            $query->where($condition);
        },
    ])
    ->paginate();

Field

If serie is a field, it is a bit more complicated to paginate, but you can still do it if the select clause only contains aggregated columns. And it's preferable to add an order by clause.

use Illuminate\Support\Facades\DB;

// -----

Invoice::select([
    'serie',
    DB::raw('min(column1)'),
    DB::raw('max(column2)'),
    DB::raw('avg(column3)'),
    // ...
])
    ->groupBy('serie')
    ->orderBy(DB::raw('min(column1)'), 'asc') // or desc
    ->paginate();

Upvotes: 0

Sumit Madaan
Sumit Madaan

Reputation: 31

Use This with any Laravel version. Its working for me

$draws = Draw::with('location')->select('location_id', 'draw_number', 'created_at')
        ->paginate(30);

$draws->setCollection($draws->groupBy(function ($date) {
    return Carbon::parse($date->created_at)->format('Y-m-d');
}));

Upvotes: 3

MohKoma
MohKoma

Reputation: 1472

Unfortunately, Laravel doesn't support pagination for Eloquent's groupBy as it's complicated case. But here is a tip, In case of you use groupBy of the collection after fetching the data and you want to keep the pagination object to look the same.

// Get the activities
$activities = $user->activities()->paginate(6);

// This will replace data property of the pagination
$activities->setCollection($activities->groupBy('type'));

Now the $activities will look like same as before you group it

{
   "current_page": 1,
   "data": {
       "type_1": [
           ...
       ],
       "type_2": [
           ...
       ]
   },
   "first_page_url": "http://URL? 
   page=1",
   "from": 1,
   "last_page": 
   ...
}

Upvotes: 4

Donkarnash
Donkarnash

Reputation: 12845

Laravel does not support groupBy with pagination

enter image description here

Read more at Laravel docs:

Update

The above warning has been removed from Laravel 8.x docs. And @ronline confirms in the comment that pagination operations that use a groupBy have been sorted out since then.

Upvotes: 7

ronline
ronline

Reputation: 2411

I am using Laravel Query Builder for : cross join, group by, pagination and it works. Here is working code.

Thread::join('participants as p', 'p.id', 'threads.id' )
   ->join('messages as m', 'm.thread_id', 'threads.id' )
   ->join('users as u', 'm.user_id', 'u.id' )
   ->select
   (
       array(
           'u.email as creator',
           'p.*',
           'threads.*',
           'threads.id as id'
          )
   )
   ->latest('threads.updated_at')
   ->groupBy('threads.id')
   ->paginate($numberOfPages)

Upvotes: 0

Peppermintology
Peppermintology

Reputation: 10220

Taken from the Laravel documentation here:

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: 0

Related Questions