Laravel query with max and group by
<p>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.</p>

<p>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</p>

<pre><code>$RS = $this-&gt;instance-&gt;user()
        -&gt;with(['driver.delivery' =&gt; function($query) use ($week_array) {
           $query-&gt;where('delivery_date', [Carbon\Carbon::parse($week_array['date'])-&gt;toDateTimeString()]);
             }])
        -&gt;with(['driver.trailer', 'driver.truck', 'driver.trailer.trailerType'])-&gt;get();
</code></pre>

<p>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.</p>

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

<pre><code>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) &gt;= '2016-05-10')
</code></pre>