php - Laravel query with max and group by -

i need create select query in laravel 5.1 have no problems creating via regular sql , wondering if me write in laravel.

i created query gets users have truck, trailer , delivery_date equals particular date (comes $week_array). working, missing components

$rs = $this->instance->user()         ->with(['driver.delivery' => function($query) use ($week_array) {            $query->where('delivery_date', [carbon\carbon::parse($week_array['date'])->todatetimestring()]);              }])         ->with(['driver.trailer', 'driver.truck', 'driver.trailer.trailertype'])->get(); 

i need exclude drivers have max delivery date equals or greater selected delivery date in query above. normal query need plug-in laravel.

in other words, need convert following query (simplified) laravel:

select      *     users         inner join     drivers on drivers.user_id = users.id         inner join     deliveries on deliveries.driver_id = drivers.id     1 = 1         , deliveries.driver_id not in (select              driver_id                     deliveries         group driver_id         having max(delivery_date) >= '2016-05-10') 

you're looking wherehas. try:

$date = carbon\carbon::parse($week_array['date'])->todatetimestring();  $rs = $this->instance->user()     ->with(['driver.delivery' => function($query) use ($date) {         $query->where('delivery_date', [$date]);     }])     ->with(['driver.trailer', 'driver.truck', 'driver.trailer.trailertype'])     ->wherehas('driver.delivery', function($query) use ($date) {         return $query->where('delivery_date', '>', $date);     }, '=', 0)     ->get(); 

also try validating query looks right replacing ->get() ->tosql() , using dd helper function.