I am having trouble implementing the group_by and having queries using Eloquent in Laravel.
Here is the scenario:
orders
- id
- qty
deliveries
- id
- qty
- order_id
I want to use a join to display the orders with incomplete deliveries as well as the corresponging balance:
Order::left_join('deliveries', 'orders.id', '=', 'deliveries.order_id')
->select(array('orders.*'), DB::raw('orders.qty - IFNULL(sum(deliveries.qty),0) AS balance')))
->group_by('order_id')
->having('balance', '>', 0)
->get();
The 'balance' value works fine without the 'having' clause. On adding the 'having' clause however, the resulting table doesn't display any rows. Does anyone have any ideas?
Thanks in advance!
Ended up switching to Laravel 4 and doing the ff which seemed to work.
Order::leftJoin('deliveries', 'orders.id', '=', 'deliveries.order_id')
->select(array('orders.*'), DB::raw('orders.qty - IFNULL(sum(deliveries.qty),0) AS balance')))
->groupBy('order_id')
->havingRaw('balance > 0')
->get();