title: Laravel querying many to many records with eager loading conditions
body: <p>So my app has structure like so.</p>
  <ul>
  <li><strong>payments</strong> hasAndBelongsToMany <strong>payers</strong></li>
  <li><strong>payers</strong> belongsTo <strong>users</strong> (<strong>users</strong> hasMany <strong>payers</strong>)</li>
  </ul>
  <p>I want to query all the payments (and the pivot data) for a certain user ID. I have been using this query that returns all the payments, regardless of user_id:</p>
  <pre><code>$payment_data = Payment::with('payers')-&gt;get();
  </code></pre>
  <p>Now, if I want to only get payments whose payers have a user_id of, say, 5 I tried this:</p>
  <pre><code>$payment_data = Payment::with(array(
          'payers' =&gt; function($q){
             $q-&gt;where('user_id', '=', 5);
          }))-&gt;get();
  </code></pre>
  <p>But it returns almost the same result except the condition only applies to the <strong>payers</strong>, not the payments i.e. I still get all payments in the payments table, just not the pivot data if the <code>user_id</code> condition is not fulfilled. I can technically just use this result and filter out the ones where the <code>payers</code> sub-array is empty but this would become very inefficient with large numbers of payments.</p>
  <p>What's the correct way to form this query?</p>
  <h3>Edit</h3>
  <p>Here is a basic representation of my db structure:</p>
  <p><img src="https://i.stack.imgur.com/mFrVs.png" alt="enter image description here" /></p>
  <p>And the plain SQL query would be something like this (RDMS varies but you get the idea):</p>
  <pre><code>SELECT payments.*, payers_payments.* 
  FROM   payments 
         RIGHT JOIN (payers 
                     LEFT JOIN payers_payments 
                            ON payers.id = payers_payments.payer_id) 
                 ON payments.id = payers_payments.payment_id 
  WHERE  payers.user_id = 5;
  </code></pre>
  <p>Using <code>wherehas</code> gives this result:</p>
  <pre><code>SELECT * 
  FROM   &quot;payments&quot; 
  WHERE  (SELECT Count(*) 
          FROM   &quot;payers&quot; 
                 INNER JOIN &quot;payer_payment&quot; 
                         ON &quot;payers&quot;.&quot;id&quot; = &quot;payer_payment&quot;.&quot;payer_id&quot; 
          WHERE  &quot;payer_payment&quot;.&quot;payment_id&quot; = &quot;payments&quot;.&quot;id&quot; 
                 AND &quot;payers&quot;.&quot;user_id&quot; = '1') &gt;= '1' 
  </code></pre>
  <p>Which is not what I want.</p>
  <h3>Edit 2</h3>
  <p>OK so after much fiddling I know that I need to use eager loading as I need the pivot fields as well as the fields from the payment model itself. Here's an example showing 3 payments and their pivot data:</p>
  <pre><code>Array
  (
      [0] =&gt; Array
          (
              [id] =&gt; 2
              [payment_date] =&gt; 2014-03-07
              [company] =&gt; Franco Manca
              [item] =&gt; Pizza
              [created_at] =&gt; 2014-03-10 10:16:08
              [updated_at] =&gt; 2014-03-10 10:16:08
              [payers] =&gt; Array
                  (
                      [0] =&gt; Array
                          (
                              [id] =&gt; 1
                              [name] =&gt; tim
                              [email] =&gt; tim@tim.com
                              [user_id] =&gt; 1
                              [created_at] =&gt; 2014-03-10 10:07:23
                              [updated_at] =&gt; 2014-03-10 10:07:23
                              [pivot] =&gt; Array
                                  (
                                      [payment_id] =&gt; 2
                                      [payer_id] =&gt; 1
                                      [amount] =&gt; 21.0
                                      [pays] =&gt; 0
                                      [created_at] =&gt; 2014-03-10 10:16:08
                                      [updated_at] =&gt; 2014-03-10 10:27:45
                                  )
  
                          )
  
                      [1] =&gt; Array
                          (
                              [id] =&gt; 2
                              [name] =&gt; tom
                              [email] =&gt; tom@tom.com
                              [user_id] =&gt; 1
                              [created_at] =&gt; 2014-03-10 10:16:35
                              [updated_at] =&gt; 2014-03-10 10:16:35
                              [pivot] =&gt; Array
                                  (
                                      [payment_id] =&gt; 2
                                      [payer_id] =&gt; 2
                                      [amount] =&gt; 0.0
                                      [pays] =&gt; 1
                                      [created_at] =&gt; 2014-03-10 10:27:45
                                      [updated_at] =&gt; 2014-03-10 10:27:45
                                  )
  
                          )
  
                  )
  
          )
  
      [1] =&gt; Array
          (
              [id] =&gt; 3
              [payment_date] =&gt; 2014-03-05
              [company] =&gt; Kaff
              [item] =&gt; Cocktail
              [created_at] =&gt; 2014-03-10 10:17:05
              [updated_at] =&gt; 2014-03-10 10:17:05
              [payers] =&gt; Array
                  (
                      [0] =&gt; Array
                          (
                              [id] =&gt; 1
                              [name] =&gt; tim
                              [email] =&gt; tim@tim.com
                              [user_id] =&gt; 1
                              [created_at] =&gt; 2014-03-10 10:07:23
                              [updated_at] =&gt; 2014-03-10 10:07:23
                              [pivot] =&gt; Array
                                  (
                                      [payment_id] =&gt; 3
                                      [payer_id] =&gt; 1
                                      [amount] =&gt; 12.0
                                      [pays] =&gt; 1
                                      [created_at] =&gt; 2014-03-10 10:17:05
                                      [updated_at] =&gt; 2014-03-10 10:17:05
                                  )
  
                          )
  
                      [1] =&gt; Array
                          (
                              [id] =&gt; 2
                              [name] =&gt; tom
                              [email] =&gt; tom@tom.com
                              [user_id] =&gt; 1
                              [created_at] =&gt; 2014-03-10 10:16:35
                              [updated_at] =&gt; 2014-03-10 10:16:35
                              [pivot] =&gt; Array
                                  (
                                      [payment_id] =&gt; 3
                                      [payer_id] =&gt; 2
                                      [amount] =&gt; 19.0
                                      [pays] =&gt; 1
                                      [created_at] =&gt; 2014-03-10 10:17:05
                                      [updated_at] =&gt; 2014-03-10 10:17:05
                                  )
  
                          )
  
                  )
  
          )
  
  )
  </code></pre>
  <p>The problem is, is if I then change the user_id condition to be, say, 99999 (which doesn't exist), it still returns all the payments with the <code>payers</code> array for each being empty, when in fact it should return a completely empty result.</p>
  <p>The 2 queries produced for the eager loading are:</p>
  <pre><code>select * from &quot;payments&quot; --I want to add my user_id condition to this
  </code></pre>
  <p>and</p>
  <pre><code>SELECT &quot;payers&quot;.*, 
         &quot;payer_payment&quot;.&quot;payment_id&quot; AS &quot;pivot_payment_id&quot;, 
         &quot;payer_payment&quot;.&quot;payer_id&quot;   AS &quot;pivot_payer_id&quot;, 
         &quot;payer_payment&quot;.&quot;amount&quot;     AS &quot;pivot_amount&quot;, 
         &quot;payer_payment&quot;.&quot;pays&quot;       AS &quot;pivot_pays&quot;, 
         &quot;payer_payment&quot;.&quot;created_at&quot; AS &quot;pivot_created_at&quot;, 
         &quot;payer_payment&quot;.&quot;updated_at&quot; AS &quot;pivot_updated_at&quot; 
  FROM   &quot;payers&quot; 
         INNER JOIN &quot;payer_payment&quot; 
                 ON &quot;payers&quot;.&quot;id&quot; = &quot;payer_payment&quot;.&quot;payer_id&quot; 
  WHERE  &quot;payer_payment&quot;.&quot;payment_id&quot; IN ( '2', '3' ) 
         AND &quot;user_id&quot; = '1'  --This satisfies the condition
  </code></pre>