Groupby in laravel leave grouped items in object

Question

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?

then I will get only 1 result of each 'type' [lnik to code](https://i.imgur.com/nePrlh9.png) — Ferhnir, Jan 21 '18 at 19:46
Okay, can you try adding orderBy two times, first time with ID and second is "types". Refer [link](https://stackoverflow.com/questions/17006309/how-to-use-order-by-for-multiple-columns-in-laravel-4) and without groupBy — harisdev, Jan 21 '18 at 19:51
not sure if is that wha u want me to do: [link to code](https://i.imgur.com/StaQNrs.png) and results: [link to code](https://i.imgur.com/ogZv78V.png) — Ferhnir, Jan 21 '18 at 20:05

apokryfos · Accepted Answer · 2018-01-21T20:19:42.023

6

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 ];
                          });
                     });

edited Jan 21 '18 at 20:19

answered Jan 21 '18 at 19:57

apokryfos

38,771
9
70
114

{ "message": "Unknown column 'families.family_id' in 'on clause'" } – Ferhnir Jan 21 '18 at 20:06
I just added a `map` to your existing (working?) query. This would not have caused that message. You do seem to be missing a join with the `families` table in the query in your question. Did you mean to write `insects.family_id` instead? – apokryfos Jan 21 '18 at 20:09
[my code](https://i.imgur.com/LG6PFaS.png), so first code which I set as a comment works, but your code throw that error... :( – Ferhnir Jan 21 '18 at 20:11
Actually u r right, my bad (in leftJoin) I didn't change family_id on type_id, and guess what, IT WORKS :) Results [link to code](https://i.imgur.com/Af7yRbZ.png) – Ferhnir Jan 21 '18 at 20:13
1

Can you change in that code 'families.family_id' to 'families.type_id' ? – Ferhnir Jan 21 '18 at 20:19
Take a look at: https://laravel.com/docs/5.5/eloquent-relationships you can presumably have a model for families and then do `Families::with([ "insects" => function ($q) { return $q->select("id", "name"); }])->get()` it will probably end up in the same result. There's going to be one more query though but depending on your data structures that might be more efficient. – apokryfos Jan 21 '18 at 20:38

score 0 · Answer 2 · edited Dec 17 '19 at 17:54

0

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

edited Dec 17 '19 at 17:54

marc_s

732,580
175
1,330
1,459

answered Jan 21 '18 at 19:40

Nate

114
1
9

{ "message": "Unknown column 'families.family_id' in 'on clause'" } [link to code](https://i.imgur.com/lNTkqrq.png) – Ferhnir Jan 21 '18 at 19:43
BUT: protected $hidden = ['types']; helped, thank you v much :) – Ferhnir Jan 21 '18 at 19:49
Can you add ->toSql() on to the query and post the result for me? – Nate Jan 21 '18 at 19:50
then I receiving a plain text like: "select `families`.`id`, `families`.`name` from `families` left join `types` on `types`.`id` = `families`.`family_id` group by `insects`.`id`, `types`.`name`" – Ferhnir Jan 21 '18 at 20:08
Do the query work the top one,the one with the get() rather than toSql();? – Nate Jan 21 '18 at 20:10
In first one I needed to change insects.id to types.id but then I recive only only one object of each 'types'. With second one only that plain text (sql querry) "select `families`.`id`, `families`.`name` from `families` left join `types` on `types`.`id` = `families`.`type_id` group by `types`.`id`, `types`.`name`" – Ferhnir Jan 21 '18 at 20:28

Groupby in laravel leave grouped items in object

2 Answers2