title: mysql join ON and AND to laravel eloquent
body: <p>I've been able to get the query result I need using the following raw sql:</p>
  
  <pre><code>select `person`.`id`, `full_name`, count(actions.user_id) as total
  from `persons`
  left join `actions`
  on `actions`.`person_id` = `persons`.`id` 
  and `actions`.`user_id` = $user
  where `type` = 'mp' 
  group by `persons`.`id`
  </code></pre>
  
  <p>But I haven't been able to get it working in eloquent yet.</p>
  
  <p>Based on some similar answers, I'd tried functions within <code>-&gt;where()</code> or <code>leftJoin()</code>, but the <code>count</code> of each person's actions isn't yet being filtered by <code>$user</code>. As it stands:</p>
  
  <pre><code>$query = Person::leftJoin('actions', function($q) use ($user)
          {
              $q-&gt;on('actions.person_id', 'persons.id')
                  -&gt;where('actions.user_id', $user);
          })
          -&gt;groupBy('persons.id')
          -&gt;where('type', 'foo')
          //-&gt;where('actions.user_id', '=', $user)
          -&gt;get(['persons.id', 'full_name', DB::raw('count(actions.id) as total')]);
  </code></pre>
  
  <p>I'm at least heading in <em>roughly</em> the right direction, right...?</p>
  
  <p>If it's relevant, the <code>Persons.php</code> model has two <code>actions</code> relationships:</p>
  
  <pre><code>public function actions()
  {
      return $this-&gt;hasMany('Action');
  }
  
  public function actionsUser($id)
  {
      return $this-&gt;hasMany('Action')-&gt;where('user_id', $id);
  }
  </code></pre>