Laravel Mysql - Group By

Question

I have below in my table

 id  zip_code  city              region              municipality                    created_at           updated_at  
------  --------  ----------------  ------------------  ---------------------  -------------------  ---------------------
     1      2100  København Ø       Region Hovedstaden  Københavns Kommune     2016-08-24 19:32:05    2016-08-24 19:32:05
     2      2150  Nordhavn          Region Hovedstaden  Københavns Kommune     2016-08-24 19:32:05    2016-08-24 19:32:05
     3      2200  København N       Region Hovedstaden  Københavns Kommune     2016-08-24 19:32:05    2016-08-24 19:32:05
     4      2300  København S       Region Hovedstaden  Københavns Kommune     2016-08-24 19:32:05    2016-08-24 19:32:05
     5      2400  København NV      Region Hovedstaden  Københavns Kommune     2016-08-24 19:32:05    2016-08-24 19:32:05
     6      2700  København B       Region Nordstran    Hårlev Komune          2016-08-26 13:13:56    2016-08-26 13:13:59
     7      3540  Lynge             Region Hovedstaden  Egedal Kommune         2016-08-24 19:32:05    2016-08-24 19:32:05
     8      3550  Slangerup         Region Hovedstaden  Hillerød Kommune       2016-08-24 19:32:05    2016-08-24 19:32:05
     9      3670  Veksø Sjælland    Region Hovedstaden  Egedal Kommune         2016-08-24 19:32:05    2016-08-24 19:32:05
    10      4050  Skibby            Region Hovedstaden  Frederikssund Kommune  2016-08-24 19:32:05    2016-08-24 19:32:05
    11      2610  Rødovre           Region Hovedstaden  Københavns Kommune     2016-08-24 19:32:05    2016-08-24 19:32:05

I use below code to get my result of the table.

\App\Models\Backend\AreaCodes::orderBy('region', 'desc')->get()->groupBy('region')->toArray();

So i get a result where my table is grouped by "region" this works perfect, but for some regions i have about 500 rows in the same region.

How can i split does groups into multiple smaller groups, with the same name as the region, that contains the next 100 rows.

So i get groups that look like below.

Group(Region hovedstaden) = 100 rows
Group(Region hovedstaden) = 100 rows
Group(Region hovedstaden) = 100 rows

In total i got 1400 rows in my table.

I have tried below code.

\App\Models\Backend\AreaCodes::orderBy('region', 'desc')->paginate(2)->get()->groupBy('region')->toArray();

But it gives error
Missing argument 1 for Illuminate\Support\Collection::get() Paginate dosent work in laravel if i use group_by, what can i do?

I have also checked out this link Get top n records for each group of grouped results no help here.

I hope your guys understand me :)

Kind regards.

Possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Shadow, Aug 26 '16 at 14:11
You could use the `->paginate(100)` method for the query builder. [Check the docs](https://www.laravel.com/docs/5.3/pagination#paginating-query-builder-results) — Thomas De Marez, Aug 26 '16 at 14:12
Hi @JhonnyWalker where should i place ->paginate(100) in my query. I tried this \App\Models\Backend\AreaCodes::orderBy('region', 'desc')->paginate(2)->get()->groupBy('region')->toArray(); but then i get Missing argument 1 for Illuminate\Support\Collection::get() — user3502250, Aug 26 '16 at 14:18

score 0 · Answer 1 · answered Aug 26 '16 at 15:41

\App\Models\Backend\AreaCodes::orderBy('region', 'desc')
->groupBy('region')
->paginate(2)
->toArray();

should work if you are using pagination on your view.

If you just want to limit the results you are fetching from the database you can use skip / take

App\Models\Backend\AreaCodes::orderBy('region', 'desc')
->groupBy('region')
->skip(500) // skips 500 rows
->take(500) // takes 500 rows
->get()
->toArray();

score -1 · Answer 2 · answered Aug 26 '16 at 15:30

-1

I'm not absolutely sure about it but i think paginate(2) and get() and to array() don't work all together.
I suggest you to try only paginate(2) and the paginate(2)->toArray();

answered Aug 26 '16 at 15:30

H.Kontodios

246
1
7

Laravel Mysql - Group By

2 Answers2