CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-5.1
Andrew
Andrew

Reputation: 7798

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.

In other words, I need to convert the following query (simplified) to Laravel:

SELECT 
    *
FROM
    USERS
        INNER JOIN
    DRIVERS ON DRIVERS.user_id = USERS.id
        INNER JOIN
    DELIVERIES ON DELIVERIES.driver_id = DRIVERS.id
WHERE
    1 = 1
        AND DELIVERIES.driver_id NOT IN (SELECT 
            driver_id
        FROM
            DELIVERIES
        GROUP BY driver_id
        HAVING MAX(delivery_date) >= '2016-05-10')

Upvotes: 0

Views: 1290

Answers (1)

Scopey
Scopey

Reputation: 6319

You're looking for 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 the query looks right by replacing ->get() with ->toSql() and using the dd helper function.

Upvotes: 2

Related Questions