Laravel query builder: How to perform calculation on withSum() column?

I have these Models:

Product Model:

protected $fillable = ['id', 'name', 'qty', 'parent_id'];

public function parent() {
return $this->belongsTo(self, 'parent_id');
}

public function children() {
return $this->hasMany(self, 'parent_id');
}


How can I get the diff from the parent product to its children like:

• 
  Product 1: qty = 1000;
  
  
• 
  Product 2: child of Product 1, qty = 100
  
  
• 
  Product 3: child of Product 1, qty = 300
  

Expected query result ike this:

Collection: {
all: [
App\Models\Product: {
id: 1,
qty: 1000,
sum_children: 400,
remaining: 600, // product_1_qty (1000) - sum_children (400)
},...
],
}


I have tried the withSum methods like:

Product::withSum('children as ttl_children', 'qty') ->addSelect('qty - ttl_chilren');

But I can not perform the calculation like that.

Can anyone help me or point me on the right direction? Thank you!

Continue reading...