jeudi 21 avril 2016

Laravel query with max and group by

I need to create a select query in Laravel 5.1 which I will have no problems creating via regular SQL and I am wondering if you could help me to write it in Laravel.

I created this query that gets all Users that have a truck, trailer and delivery_date equals a particular date (comes from $week_array). It is working, but it is missing some 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 to exclude those drivers that have MAX delivery date which equals or greater than selected delivery date in the query above. This is the normal query that I need to plug-in to laravel.

select driver_id, max(DATE(delivery_date)) as delivery_date 
  FROM deliveries 
WHERE instance_id = ? 
GROUP by driver_id



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire