How to get quantity?

Question

Laravel

There are some categories made according to the nested set algorithm through the . There are also devices associated with many-to-many categories. For clarity

public function devices() {
        return $this->belongsToMany("App\Models\Device", "category_device", "category_id", "device_id");
    }

The question is how to get devices in all child categories of a particular category? I understand that you can get all the children with one request, and then for each child get the number of his devices and sum it up. How to do it in the least number of requests?

UPD

It seems that this can be done in two requests

public function childrenDevices() {
        $categories = $this->descendants()->pluck("id");
        return Device::whereHas("categories", function ($query) use ($categories) {
            $query->whereIn("category_id", $categories);
        });
    }

The code above generates the following SQL

select * from `devices` where exists (select * from `categories` inner join `category_device` on `categories`.`id` = `category_device`.`category_id` where `devices`.`id` = `category_device`.`device_id` and `category_id` in (?, ?, ?) and `categories`.`deleted_at` is null) and `devices`.`deleted_at` is null

and if you call $category->childrenDevices()->count() returns 49 (Let's say $category has 3 children with id 3, 4, 5)

But if you check how many links categories have

SELECT * FROM `category_device` WHERE `category_id` in (3, 4, 5)

That number of results is 55. Also in the database there are no categories or devices marked as deleted_at. Also, if we take specifically 3, 4, 5 categories and call $category->devices()->count() for each separately, then the sum of these values ​​\u200b\u200bwill be 55. Which way to dig?

Answer 1

public function childrenDevices() {
        $categories = $this->descendants()->pluck("id");
        return Device::whereHas("categories", function ($query) use ($categories) {
            $query->whereIn("category_id", $categories);
        });
    }

This code works correctly. He problem was that several categories can contain the same device.

SELECT DISTINCT `device_id` FROM `category_device` WHERE `category_id` in (3, 4, 5)

This query also returns 49 results

Answer 2

I won’t speak for this particular package, but logically, something like

select 
count(*) 
from products 
    inner join categories 
        on products.category_id = categories .id 
          and categories.left >= $left and categories.right <= $right

where $left and $right are the fields from the nested set of the current category. well, the names of the fields must correspond to the tm that are used in the package. You can also do this using the query builder.