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
– Danyal Sandeelo

2016-08-10 07:41:47 +00:00
Commented Aug 10, 2016 at 7:41 — Danyal Sandeelo
– Danyal Sandeelo, Commented Aug 10, 2016 at 7:41
@DanyalSandeelo yes you are right

Prabhakaran
– Prabhakaran

2016-08-10 07:44:15 +00:00
Commented Aug 10, 2016 at 7:44 — Prabhakaran
– Prabhakaran, Commented Aug 10, 2016 at 7:44

Community · Accepted Answer · 2017-05-23 12:00:59Z

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, 2017 at 12:00

CommunityBot

11 silver badge

answered Aug 10, 2016 at 7:49

Aadil Damani

364 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Prabhakaran Over a year ago

It is postgresql and not mysql

Aadil Damani Over a year ago

stackoverflow.com/questions/2560946/… THis contains the equivalent method for group_concat.

Prabhakaran Over a year ago

Yes it works, but it is in different format of array like it returns {4,2} instead of [4,2]

Danyal Sandeelo · Accepted Answer · 2016-08-10 07:53:31Z

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

While in your case you won't need group, you will need group_concat. Have a look here http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

Here is what I did:

For postgreSQL use this syntax

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

edited Aug 10, 2016 at 7:53

answered Aug 10, 2016 at 7:43

Danyal Sandeelo

12.4k10 gold badges57 silver badges90 bronze badges

5 Comments

Prabhakaran Over a year ago

but I don't think this will give me the expected output

Prabhakaran Over a year ago

actually it is Postgresql

Prabhakaran Over a year ago

string_agg is not a postgresql function it throws exception

Danyal Sandeelo Over a year ago

@overflow have a look here.. stackoverflow.com/questions/2560946/…

Danyal Sandeelo Over a year ago

@overflow upvote or mark it as answered if it answered your question..mention postgresql in your question too.

Collectives™ on Stack Overflow

laravel eloquent query group by

2 Answers 2

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related