Grouping together related results

I am attempting an Eloquent query that's a little bit complex:

        $armorSets = ArmorSet::with([
            "armors.resources" => function ($query) {
                $query->groupBy("tier")->orderBy("tier", "asc");
            },
        ])->get();

If I remove the ->groupBy("tier") clause, then the Eloquent query works, and I get the following result on the front end (using blade), which is close to what I want:

  Hylian Set
  Hylian Hood
  Tier 1: 5 - Bokoblin Horn
  Tier 2: 5 - Bokoblin Fang
  Tier 2: 8 - Bokoblin Horn
  Tier 3: 10 - Bokoblin Fang
  Tier 3: 5 - Bokoblin Guts
  Tier 4: 15 - Bokoblin Guts
  Tier 4: 30 - Amber

However, I want each tier to be combined. So, for example, Tier 2 will only be listed once, with the text like "5 - Bokoblin Fang and 8 - Bokoblin Horn".

For reference, here is the table structure and relationships:

DB tables

If there is a way to use groupBy(), that would probably be ideal, but if there's another way--by using plain ol' Query Builder or something else in Blade--I'm all ears.

SilenceBringer

3 years ago

Best Answer

Level 55

@sturm looks like the armors and resources relationship is BelongsToMany. you're trying to groupBy tier, but tier is the column in pivot table, so, it will not works this way.

The easiest way will be to manipulate the data after loading, like

        $armorSets = ArmorSet::with([
            "armors.resources" => function ($query) {
                $query->withPivot("tier");
            },
        ])->get();

and in blade something like

@foreach ($armorSets as $armorSet)
	@foreach ($armorSet->armors as $armor)
		@foreach ($armor->resources->groupBy(fn ($resource) => $resource->pivot->tier)->sortKeys as $tier => $resources)
			Tier {{ $tier }}: {{ $resources->pluck('name')->join(',', 'and') }}
		@endforeach
	@endforeach
@endforeach

1 like

Sturm

3 years ago

Level 5

Thank you so much, @silencebringer! With a few tweaks to your answer, I was able to make it work the way I need it to.

First, I noticed that your ->sortKeys() was missing the parentheses at the end. That tripped me up for a moment. However, I don't really need to do the sorting in Laravel since I'm doing so in the DBMS, which I've been told is more performant.

        $armorSets = ArmorSet::with([
            "armors.resources" => function ($query) {
                $query->orderBy("tier", "asc");
            },
        ])->get();

        return view("index", compact("armorSets"));

Then, rather than plucking from a collection, I wanted the armor_resource.quantity_needed field to be next to each resource needed. Thus, one more nested @foreach was necessary, leaving me with quite the nested loops:

<div>
    @foreach($armorSets as $armorSet)
        <div>
            <h2>{{ $armorSet->name }}</h2>
            @foreach($armorSet->armors as $armor)
                <h3>{{ $armor->name }}</h3>
                @if($armor->upgradable)
                    @foreach($armor->resources->groupBy(fn($resource) => $resource->pivot->tier) as $tierNum => $resources)
                        <p>Tier {{ $tierNum }}:</p>
                        @foreach($resources as $resource)
                            <p>{{ $resource->pivot->quantity_needed }} {{ $resource->name }}</p>
                        @endforeach
                    @endforeach
                @endif
            @endforeach
        </div>
    @endforeach
</div>

I also adjusted a couple of variable names there for clarity. In the end, that gives me exactly what I was looking for:

Hylian Set
Hylian Hood
Tier 1:
5 Bokoblin Horn
Tier 2:
5 Bokoblin Fang
8 Bokoblin Horn
Tier 3:
10 Bokoblin Fang
5 Bokoblin Guts
Tier 4:
15 Bokoblin Guts
30 Amber

Obviously, I have no styling yet; I'm just working out the logic and Eloquent queries first. But this gives me a great start to my new project. Thank you again!

Please or to participate in this conversation.