title: Laravel 5.3 Eloquent Join table with multiple condition join having count
body: <p>I have question about left join table which using having count to select main table to be shown with the condition described as below:</p>
  
  <pre><code>select  b.id, b.location_name, b.box_identity
  from    box b
      left join device d on b.id = d.box_id
                        and d.deleted_at is null
  group by b.id, b.location_name, b.box_identity
  having count(d.*) &lt; COALESCE(b.device_slot, 3)
  order by b.id desc
  </code></pre>
  
  <p>i have tried using eloquent code like this, but DB::raw is not working in $join function</p>
  
  <pre><code>$boxes = Box::leftJoin('device', function($join) {
      $join-&gt;on('box.id', '=', 'device.box_id');
      $join-&gt;on(DB::raw('device.deleted_at is null'));
  })
  -&gt;select('box.id', 'box.box_identity', 'box.location_name')
  -&gt;groupBy('box.id', 'box.box_identity', 'box.location_name')
  -&gt;havingRaw('COUNT(device.*) &lt; COALESCE(box.device_slot, 3)')
  -&gt;orderBy('box.id', 'desc')
  -&gt;get();
  </code></pre>
  
  <p>How can i achieve this query using laravel eloquent? Thanks in advance!</p>