Eloquent Model Join Table and Sub Query

I have few questions about the Laravel Eloquent Model. Assume that I have few tables at the bottom OrderDetail od_id p_id unitprice qty

Product p_id name price pc_id

Product Category pc_id category

Expected output query

SELECT P.name,IFNULL(0.00,(SELECT SUM(OD.unitprice*OD.qty) FROM OrderDetail AS OD WHERE O.p_id = P.pid GROUP BY OD.p_id))
FROM products AS P INNER JOIN ProductCategory AS PC ON
p.pc_id = PC.pc_id WHERE PC.category = 'Snack';

Attempts: Model Product Model

public function order(){
    return $this->belongsTo(OrderDetail::class);
}

public function category(){
  return $this->belongsTo(ProductCategory::class);
}

OrderDetail Model

public function product(){
   return $this->hasMany(Product::class);
}

ProductCategory Model

public function product(){
   return $this->hasMany(Product::class);
}

Pull Data Controller

Product::with("category")->where("ProductCategory.category","Snack")->get();

But I am stucking to attach the sub query inside to the select column...

Thanks.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/32hRMde
via IFTTT