Aug 30, 2022

Level 5

Using groupBy with a sum in a many-to-one relationship

There's no shortage of "groupBy" questions here, but I have been thus far unable to find an existing question similar enough to my own.

I have two tables in a many-to-one relationship: Table Relationship The armor_resource table is a pivot table, but I've also created a Requirement model for it so that I can access its records by id directly with Eloquent. The first three entries in the table refer to two resources with a quantity_needed for each: Requirements Those two Resources:

2 - Bokoblin Horn

3 - Bokoblin Fang

So, what is retrieved from the database is essentially:

5 Bokoblin Horns
8 Bokoblin Horns
5 Bokoblin Fangs

I'd like to group the results by the Resource name to get:

13 Bokoblin Horns
5 Bokoblin Fangs

It seems like this should be doable in Eloquent, so I have the beginnings of a query like so:

        $requirements = Requirement::with(["resource" => function ($query) {
            $query->groupBy("name");
        }])
            ->sum("quantity_needed")
            ->findMany([1, 2, 3]);

This clearly doesn't work since I am just really bad with groupBy.

How can I group the results by resource->name and sum up their associated requirement->quantity_neededs?

rodrigo.pedra

3 years ago

Level 56

I propose two solutions, one a bit different from what you've been trying and the other more alike.

Please read/watch this references:

Solution 1

class Resource extends Model
{
    // add a has many relation to the pivot table
    public function requirements()
    {
        return $this->hasMany(Requirement::class);
    }
}

$requirements = Resource::query()
    ->withSum('requirements', 'quantity_needed') // use withSum
    ->findMany([2, 3]);

Solution 2

// as we are grouping by resource_id, we will get 2 records back,
// as they were aggregated to sum `quantity_needed`
$requirements = Requirement::query()
    ->with(['resource'])
    ->select([
        'resource_id',
        DB::raw('SUM(quantity_needed) AS quantity_needed'),
    ])
    ->groupBy('resource_id')
    ->findMany([1, 2, 3]);

Sturm

3 years ago

Level 5

Thank you for the suggestions, @rodrigo.pedra. I like the first one, as I already have the relationships written in their respective models. Unfortunately, the ->findMany([2, 3]) clause is operating on the Resource model, and those IDs should be for the Requirements model instead.

It is very close to what I need, but I seem to be unable to constrain the results by the IDs of the Requirements. I've tried adding a whereHas clause, an addition with clause, etc. Can this one be modified just a bit to put that ->findMany([2, 3]) on Requirements?

I haven't yet tried your second solution; if I have some time this evening, I'll give it a shot as well.

Sturm

3 years ago

Level 5

I gave the first one a try again, this time implementing withSum() properly. (I had a syntax error in it for a while.)

        $resources = Resource::whereHas("requirements", function ($query) use ($sessionRequirements) {
            $query->whereIn("id", $sessionRequirements);
        })
            ->withSum(["requirements" => function ($query2) use ($sessionRequirements) {
                $query2->whereIn("id", $sessionRequirements);
            }], "quantity_needed")
            ->get();

This actually works!

It's a tad bit clunky, though, so I welcome any suggestions for cleaning it up.

rodrigo.pedra

3 years ago

Level 56

What about this?

$resources = Resource::query()
    ->whereHas('requirements', fn ($query) => $query->whereKey($sessionRequirements))
    ->withSum([
        'requirements' => fn ($query) => $query->whereKey($sessionRequirements),
    ], 'quantity_needed')
    ->get();

1 like

Sturm

3 years ago

Level 5

@rodrigo.pedra Yep, that's perfect! Well, the query()-> part is unnecessary, but otherwise, that does the trick quite well. It still seems odd to me that I have to define that same conditional query twice, but I guess that's the only way to do what I'm looking for.

By the way, I could not find any official documentation for the whereKey() method, only some blog posts and Medium articles about it, usually when referring to Laravel 5.4. It does seem to work, though, so I guess I'll keep that one in my toolbelt.

Thanks again, @rodrigo.pedra!

1 like

rodrigo.pedra

3 years ago

Level 56

@Sturm glad it helped.

I usually go for Model::query() so:

IDE autocompletion works better
fluent calls are all aligned accordingly
It is "unecessary" in the sense the Model class forward calls using the magic methods __call() and __callStatic(). So although it is a micro-optimization, it will avoid some calls to the magic methods and then to the proxied object. At the end summing up the benefits from the points above, with so few keystrokes to save some under the hood magic, makes it to a no-brainier to me.

At the end, it is a matter of taste.

You could try to get away of specifying the condition twice by using a subquery, but I actually think it would be a lot more complex while adding no to little benefit in performance. I also try to avoid this kind of repetition, but rules can be broken sometimes.

As for ->whereKey(), I use it for so long I can't remember where I learned about it. Maybe from Laravel code base as I like to keep an eye on merged PRs. But I agree Laravel has a lot of undocumented gems like this.

Have a nice day =)

Please or to participate in this conversation.