This is what I have done so far.
DB::table('products')
->leftjoin('inventories', 'inventories.product_id', 'products.id')
})
->leftjoin('order_items as purchase_oi', function($query){
$query->on('purchase_oi.inventory_id', 'inventories.id');
$query->on('purchase_oi.status','!=', \DB::raw('"cancelled"'));
})
->select(DB::raw('sum(inventories.quantity)as qty'),
DB::raw('count(purchase_oi.inventory_id) as purchases'),
'products.id as pid', 'products.*')
->where('products.is_deleted', 0)
->where('inventories.is_deleted',0)
->groupBy('inventories.product_id')
->get();
order_items table contains the status in
enum('shipped','return''cancelled');
This issue is here on the join with order_items table. Currently, I'm just getting the data which is not equal to cancelled but I want to get the order_items data which are returned and the order_items data which are cancelled. i.e get me the purchases,cancelled and return count from order_items table. Expected output is:
0=>purchases = 4,
returns = 10,
cancelled = 2