CODEWITHSUNDEEP
phplaravel
Prabhakaran
Prabhakaran

Reputation: 3999

laravel eloquent query group by

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

enter image description here

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.

Upvotes: 0

Views: 2001

Answers (2)

Aadil Damani
Aadil Damani

Reputation: 36

$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.

Upvotes: 2

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

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:

enter image description here

For postgreSQL use this syntax

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

Upvotes: 1

Related Questions