title: Laravel eloquent query with sum of values from another table on a condition
body: <p>I have two tables. Transaction and allocation.
  one Transaction can have multiple allocation.
  Relationships defined as follows:</p>
  
  <p><strong>Inside Allocation Table</strong></p>
  
  <pre><code>class Allocation extends Model
  {
      public function transaction_fk(){
          return $this-&gt;belongsTo('App\Models\Transaction','transaction_id');
      }
  }
  </code></pre>
  
  <p><strong>Inside Transaction Table</strong></p>
  
  <pre><code>class Transaction extends Model
  {
      public function allocations() {
          return $this-&gt;hasMany('App\Models\Allocation');
      }
  }
  </code></pre>
  
  <p>I need to query all the rows from the transaction table with a certain ID along with the sum of allocations for that transaction line <strong>with sum of allocation totals if transaction total is not equal to sum of allocation totals</strong>. Something along the lines of the following: </p>
  
  <pre><code>Transaction::where('id',$id)-&gt;where('amount','!==','sum(allocations.amount)')-&gt;with('balance' as 'sum(allocations.amount)')-&gt;get();
  </code></pre>
  
  <p>This query does not work. What am I missing?</p>
  
  <p>I was able to do it as a simple query which I looped through and made a second query and added to list. It gives me the correct result. As you can see this is long-winded and slower. I need to do it straight away while querying the database one time. </p>
  
  <pre><code> $transactions2 = Transaction::where('contact_type','supplier')
      -&gt;where('contact_id',$ehead-&gt;supplier_id)
      -&gt;get();
  
      $transactions = [];
      foreach ($transactions2 as $item) {
         $sum = Allocation::where('transaction_id',$item['id'])-&gt;get()-&gt;sum('amount');
         if($item['amount'] !== $sum){
          $item-&gt;unallocated_amount = $sum;
          array_push($transactions, $item);
         }
  
      }
  </code></pre>