how to sum in subquery in laravel

Question

i want to sum in addselect() function but it show me error. I have 2 model as see there:

1.jewelItem model:

protected $table = 'jewel_items';

public function buyInvoice(){
    return $this->belongsTo(BuyInvoice::class,'buy_invoice_id');
}

2.buyInvoice model:

protected $table = 'buy_invoices';

public function jewelsItems(){
return $this->hasMany(JewelsItems::class);
}

and every jewelItem has weight column. my query:

  $buyInvoice=BuyInvoice::addSelect(['allWeight'=>JewelsItem::whereColumn('buy_invoices.id','buy_invoice_id')->sum('weight')
        ])->get();

but it show me this error:

Column not found: 1054 Unknown column 'buy_invoices.id' in 'where clause' (SQL: select sum(`weight`) as aggregate from `jewel_items` where `buy_invoices`.`id` = `buy_invoice_id`)

how can i fix this without using Raw method, cause as here says "Raw statements will be injected into the query as strings" and it's vulnerable.

M Khalid Junaid · Accepted Answer · 2021-02-10 18:55:58Z

1

In newer version of laravel you can use withSum to get sum of weights for related jewelsItems

$buyInvoice=BuyInvoice::withSum('jewelsItems', 'weight')
                      ->orderBy('jewelsItems_sum_weight')
                      ->get();

Or in older versions you could use withCount for sum as

 $buyInvoice= BuyInvoice::withCount([
                            'jewelsItems as allWeight' => function ($query) {
                                        $query->select(DB::raw("sum(weight)"));
                            }
                    ])->orderBy('allWeight')
                      ->get();

answered Feb 10, 2021 at 18:55

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

yassin Over a year ago

I got a new error: Call to undefined method App\Models\BuyInvoice::withSum()

M Khalid Junaid Over a year ago

@yassin So you are using older version then try the second approach withCount

yassin Over a year ago

but my dude. my laravel framwork version is 8.0.0 so is it old? and as i said i don't want to use raw method cause of it's vulnerable

Barberrysk · Accepted Answer · 2021-02-10 18:55:51Z

0

you forgot use join in this subquery

JewelsItem::whereColumn('buy_invoices.id','buy_invoice_id')->sum('weight')

answered Feb 10, 2021 at 18:55

Barberrysk

11 silver badge2 bronze badges

Comments

mrhn · Accepted Answer · 2022-03-30 09:52:36Z

0

Instead of doing a custom count subquery, Laravel has syntaxic sugar for doing this, utilizing the method withSum(). An example of that could be.

$data = Model::query()->withSum('relation.subRelation','weight')->get();

edited Mar 30, 2022 at 9:52

mrhn

19.1k6 gold badges31 silver badges48 bronze badges

answered Mar 29, 2022 at 16:14

kenan ismail

311 silver badge9 bronze badges

Collectives™ on Stack Overflow

how to sum in subquery in laravel

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related