Implementing group_by and having in Laravel using Eloquent

howellmartinez picture howellmartinez · Jun 7, 2013 · Viewed 32.7k times · Source

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!

Answer

howellmartinez picture howellmartinez · Jun 14, 2013

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();