Laravel groupBy with pagination

user_12917920 0
Viewed 4660

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?

laravel
4 Answers
user_12203640

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.

user_2812780

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

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": 
   ...
}
user_108004230
Related