Filament•12mo ago

Count relationship quantity via pivot

models: ProductPack Product ProductPackPivot (n n table): pivot extra field -> product_quantity for each ProductPack i want to know how many products it has. at the moment im doing it like this, but this only counts the relationships:

ProductPack::query()
    ->withCount('products')
    ...

ProductPack::query()
    ->withCount('products')
    ...

and i need to go to the pivot, and get the product_quantity field instead of counting the relation im kinda lost on how to achieve it. example: lets say i have a product pack that has 2 products: 4 waters 2 forks the product pack has 2 products, but really has 4+2 products, so i need to get 6 instead of 2

3 Replies

LeandroFerreira•12mo ago

something like this?

ProductPack::query()->withCount([
    'products', 
    'products as products_count' => function ($query) {
        $query->sum('product_quantity');
    }]);

ProductPack::query()->withCount([
    'products', 
    'products as products_count' => function ($query) {
        $query->sum('product_quantity');
    }]);

ericmp #2OP•12mo ago

am i missing something?

ericmp #2OP•12mo ago

the product_packs table has an id column idk why but seems this does the trick:

ProductPack::query()->withCount([
    'products as products_count' => function ($query) {
        $query->select(\Illuminate\Support\Facades\DB::raw('sum(product_quantity)'));
    }]);

ProductPack::query()->withCount([
    'products as products_count' => function ($query) {
        $query->select(\Illuminate\Support\Facades\DB::raw('sum(product_quantity)'));
    }]);

Gaming

Programming

Count relationship quantity via pivot