overflow overflow - 10 months ago 107
PHP Question

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.

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

Answer, Your answer is very closed,

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

$models_formated = array_map(function($model){
    $model['google'] = explode(',', $model['google']);
    return $model;
}, $models);

echo '<pre>';