Laravel sum nested relationship column

Question

I tried sum nested related column but get empty result.

Code:

$data = CropType::with(['categories' => function($cq) {
    return $cq->with(['varieties' => function($vq) {
        return $vq->with(['products' => function($pq) {
            return $pq->sum('total');
        }]);
    }]);
}])->get();

As you can see, I have 4 tables that are interconnected with foreign keys

Table: crop_types

id

title
Table: categories

id

title

crop_type_id
Table: varieties

id

title

category_id
Table: products

id

title

total

variety_id

But when I run my query it's run as like this instead summing total products:

"select * from crop_types"

How I can correctly sum total products in each crop type?

check this

iamab.in
– iamab.in

2021-05-15 10:00:43 +00:00
Commented May 15, 2021 at 10:00 — iamab.in
– iamab.in, Commented May 15, 2021 at 10:00

Otabek Mansurov · Accepted Answer · 2021-05-15 09:16:14Z

Try this query to get total products in each crop type:

SQL

SELECT crop_types.id,
         crop_types.title AS title,
         sum(products.total) AS total
FROM `products`
INNER JOIN `varieties`
    ON `varieties`.`id` = `products`.`variety_id`
INNER JOIN `categories`
    ON `categories`.`id` = `varieties`.`category_id`
INNER JOIN `crop_types`
    ON `crop_types`.`id` = `categories`.`crop_type_id`
GROUP BY  `crop_types`.`id`

Laravel Eloquent

$result = Product::query()
    ->selectRaw('crop_types.id, crop_types.title as title, sum(products.total) as total')
    ->join(
        'varieties',
        'varieties.id',
        '=',
        'products.variety_id'
    )
    ->join(
        'categories',
        'categories.id',
        '=',
        'varieties.category_id'
    )
    ->join(
        'crop_types',
        'crop_types.id',
        '=',
        'categories.crop_type_id'
    )
    ->groupBy('crop_types.id')
    ->get();

Collectives™ on Stack Overflow

Laravel sum nested relationship column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related