laravel eloquent query group by

Question

I have following structure, just attaching screenshot for reference, consider the attached image is my sql schema

This is what I am trying to get

$array = [
    [
        'city'  =>  1,
        'google'=>  [4,2]
    ],
    [
        'city'  =>  2,
        'google'=>  [3,2,1]
    ],
];

I have used Postgresql

I tried with group by though no logic behind my implementation, no magic involved in laravel

$models = Model::groupBy('city')->get();

Can anyone help to find the way?

Thought of doing it through loop but would like to know the efficient way of doing it.

city one should have 4 and 3 as per file,,right? – Danyal Sandeelo Aug 10 '16 at 07:41 — Danyal Sandeelo, Aug 10 '16 at 07:41
@DanyalSandeelo yes you are right – Prabhakaran Aug 10 '16 at 07:44 — Prabhakaran, Aug 10 '16 at 07:44

score 2 · Accepted Answer · edited May 23 '17 at 12:00

2

$models = Model::groupBy('city')->selectRaw('city, GROUP_CONCAT(google) as google')->get();

Try this out. This would group concat the result for mysql.

 $models = Model::groupBy('city')->selectRaw('city, array_agg(google) as google')->get();

as per here, there is an alternate for group_concat in Postgres.

edited May 23 '17 at 12:00

Community

answered Aug 10 '16 at 07:49

Aadil Damani

It is postgresql and not mysql – Prabhakaran Aug 10 '16 at 07:51
http://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent THis contains the equivalent method for group_concat. – Aadil Damani Aug 10 '16 at 07:52
Yes it works, but it is in different format of array like it returns {4,2} instead of [4,2] – Prabhakaran Aug 10 '16 at 07:57

Danyal Sandeelo · Answer 2 · 2016-08-10T07:53:31.157

1

You can query directly like this

$resultSet =  DB::select(DB::raw(" SQL QUERY HERE"));

and in models you can do it like

$resultSet = DB::table('table_name')
            ->groupBy('column_name')
            ->get();

Here is what I did:

For postgreSQL use this syntax

   SELECT city, 
   string_agg(google, ',')
   FROM test
   GROUP BY city

edited Aug 10 '16 at 07:53

answered Aug 10 '16 at 07:43

Danyal Sandeelo

but I don't think this will give me the expected output – Prabhakaran Aug 10 '16 at 07:45
actually it is Postgresql – Prabhakaran Aug 10 '16 at 07:47
string_agg is not a postgresql function it throws exception – Prabhakaran Aug 10 '16 at 07:57
@overflow have a look here.. http://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent – Danyal Sandeelo Aug 10 '16 at 07:58
@overflow upvote or mark it as answered if it answered your question..mention postgresql in your question too. – Danyal Sandeelo Aug 10 '16 at 08:05

2 Answers2