CODEWITHSUNDEEP
phplaravelgroup-by
Ferhnir
Ferhnir

Reputation: 33

Groupby in laravel leave grouped items in object

Right, not sure if I doing something wrong or it's a problem with Illuminate\Database in Laravel.

My code:

$sth = Insect::leftJoin('types', 'types.id', '=', 'families.type_id')
            ->select('types.name as types','families.id','families.name')
            ->get()
            ->groupBy('types');

Result before groupBy is:

[
{
    "types": "moths",
    "id": 1,
    "name": "Bombycidae"
},
{
    "types": "moths",
    "id": 2,
    "name": "Brahmaeidae"
},
{
    "types": "moths",
    "id": 3,
    "name": "Cossidae"
},
{
    "types": "larvas",
    "id": 6,
    "name": "test"
}]

But with groupBy:

{
"moths": [
    {
        "types": "moths",
        "id": 1,
        "name": "Bombycidae"
    },
    {
        "types": "moths",
        "id": 2,
        "name": "Brahmaeidae"
    },
    {
        "types": "moths",
        "id": 3,
        "name": "Cossidae"
    }
],
"larvas": [
    {
        "types": "larvas",
        "id": 6,
        "name": "test"
    }
]
}

So my problem is, I want to get rid of that types in objects ...

Any ideas?

Upvotes: 2

Views: 7987

Answers (2)

Nate
Nate

Reputation: 114

You can remove the select type, and instead do the following:

$sth = Insect::leftJoin('types', 'types.id', '=', 'families.family_id')
    ->select('families.id','families.name')
    ->groupBy('insects.id', 'types.name')
    ->get();

Also, to see the raw query in action you can use the following

$sth = Insect::leftJoin('types', 'types.id', '=', 'families.family_id')
    ->select('families.id','families.name')
    ->groupBy('insects.id', 'types.name')
    ->toSql();

dd($sth);

You were selecting columns to use in your object, so if you remove it from the select statement, it will no longer show in your objects, you can also add the type to the $hidden input in your types model like so:

/**
 * The attributes that should be hidden for arrays.
 *
 * @var array
 */
protected $hidden = [
    'name',
];

Grouping in laravel: https://laravel.com/docs/5.5/queries#ordering-grouping-limit-and-offset

Hiding rows from Arrays: https://laravel.com/docs/5.5/eloquent-serialization#hiding-attributes-from-json

Upvotes: 0

apokryfos
apokryfos

Reputation: 40653

Ok first of all what you're doing is calling groupBy on the resulting collection and this is not related to the GROUP BY MySQL query clause which is very badly named (not relevant but just worth noting).

You can just map the result to what you need:

 $sth = Insect::leftJoin('types', 'types.id', '=', 'families.type_id')
                    ->select('types.name as types','families.id','families.name')
                    ->get()
                    ->groupBy('types')->map(function ($group) {
                          return $group->map(function ($value) {
                                return [ "id" => $value->id, "name" => $value->name ];
                          });
                     });

Upvotes: 6

Related Questions