title: laravel eloquent JOIN ON multiple condition
body: <p>i have a table call <strong>bank</strong>, which store the country bank name, code, etc</p>
  
  <p>i have a table call <strong>site_banks</strong>, which store the bank account, bank holder name and foreign <strong>bank_id</strong> which refer to <strong>bank</strong></p>
  
  <p>i have a table call <strong>deposit_records</strong>, which store deposit time, deposit receipt, status, etc and foreign <strong>bank_id</strong> which refer to <strong>site_banks</strong></p>
  
  <p>for <strong>deposit_records</strong>, the status field, value 1 mean successfully</p>
  
  <p>now i want to calculate the total income for each <strong>site_banks</strong></p>
  
  <p>but i don't want each row <strong>site_banks</strong> call one query to count, this will have a lots of sql query call</p>
  
  <p>so i think i can use the <strong>join</strong>, below is the <strong>site_banks</strong> model file, the <strong>scopeGroupAllSiteBanksByBranch</strong> is where i want to collect all site_banks, join with SUM, return in array</p>
  
  <p>but even i have about 200 <strong>site_banks</strong> rows, a lots of <strong>deposit_records</strong> rows which is <strong><em>status = '1'</em></strong>, but the <strong>scopeGroupAllSiteBanksByBranch</strong> will always return only one row, i don't understand why, i copy the sql query to run in mysql, is same result, only 1 row return.</p>
  
  <pre><code>&lt;?php
  
  use Illuminate\Database\Eloquent\SoftDeletingTrait;
  
  class SiteBanks extends \LaravelBook\Ardent\Ardent {
  
      use SoftDeletingTrait;
  
      /**
       * The database table used by the model.
       *
       * @var string
       */
      protected $table = 'site_banks';
  
      protected $dates = ['deleted_at'];
  
      protected $fillable = array('bank_id', 'bank_name', 'bank_account', 'bank_holder', 'bank_username', 'bank_password', 'min_deposit', 'max_deposit', 'daily_max_deposit');
  
      public static $rules = array(
          'bank_id' =&gt; 'required|exists:banks,id',
          'bank_name' =&gt; 'required|min:1',
          'bank_account' =&gt; 'required|min:1|numberonly',
          'bank_holder' =&gt; 'required|min:1',
          'bank_username' =&gt; 'required|min:1',
          'bank_password' =&gt; 'required|min:1',
          'min_deposit' =&gt; 'required|fund',
          'max_deposit' =&gt; 'required|fund',
          'daily_max_deposit' =&gt; 'required|fund',
      );
  
      public function bank() {
          return $this-&gt;belongsTo('Banks', 'bank_id');
      }
  
      public function transactions() {
          return $this-&gt;hasMany('Deposit', 'bank_id', 'id');
      }
  
      public function transactionsDone() {
          return $this-&gt;hasMany('Deposit', 'bank_id', 'id')-&gt;where('status', '=', 1);
      }
  
      public function transactionsBanned() {
          return $this-&gt;hasMany('Deposit', 'bank_id', 'id')-&gt;where('status', '=', 2);
      }
  
      public function transactionsPending() {
          return $this-&gt;hasMany('Deposit', 'bank_id', 'id')-&gt;where('status', '=', 0);
      }
  
      public function setBankPasswordAttribute($value) {
          $this-&gt;attributes['bank_password'] = Crypt::encrypt($value);
      }
  
      public function getBankPasswordAttribute() {
          return Crypt::decrypt($this-&gt;attributes['bank_password']);
      }
  
      public function scopeGetAvailableBanks($query) {
          $query-&gt;where(DB::raw("(current_deposit &lt; daily_max_deposit) OR (current_deposit IS NULL) OR (DATE(last_deposit) != DATE(NOW()) OR last_deposit IS NULL) OR (DATE(last_deposit) = DATE(NOW()) &amp;&amp; current_deposit &lt; daily_max_deposit)"));
          $query-&gt;groupBy('bank_id')-&gt;orderBy('id', 'ASC');
      }
  
      public function scopeGroupAllSiteBanksByBranch($query) {
          $bank = \SiteBanks::withTrashed()
              -&gt;leftJoin('deposit_records', function($q) {
                  $q-&gt;on('deposit_records.bank_id', '=', 'site_banks.id');
                  $q-&gt;where('deposit_records.status', '=', 1, 'and');
              })
              -&gt;select(array('site_banks.*', DB::raw('SUM(`deposit_records`.`deposit_amount`) AS `total_income`')))
              -&gt;get();
  //        $bank = \SiteBanks::withTrashed()-&gt;get();
          $bank-&gt;load('bank');
  
          $banks = array();
          foreach($bank as $key =&gt; $var) {
              $arr = array();
  
              $arr = $var-&gt;toArray();
              $arr['income'] = $var-&gt;total_income;
              $banks[$var-&gt;bank-&gt;bank_name][] = $arr;
          }
  
          return $banks;
      }
  
  }
  </code></pre>
  
  <p><strong><em>SQL Query</em></strong></p>
  
  <blockquote>
    <p>select <code>site_banks</code>.*, SUM(<code>deposit_records</code>.<code>deposit_amount</code>) AS
    <code>total_income</code> from <code>site_banks</code> left join <code>deposit_records</code> on
    <code>deposit_records</code>.<code>bank_id</code> = <code>site_banks</code>.<code>id</code> and
    <code>deposit_records</code>.<code>status</code> = '1'</p>
  </blockquote>
  
  <p><strong><em>var_dump</em></strong> the result from <strong>scopeGroupAllBanksByBranch</strong></p>
  
  <pre><code>array (size=1)
    'BANKNAME' =&gt; 
      array (size=1)
        0 =&gt; 
          array (size=20)
            'id' =&gt; int 1
            'bank_id' =&gt; int 4
            'bank_name' =&gt; string 'BANKNAME' (length=6)
            'bank_account' =&gt; string '123456789' (length=9)
            'bank_holder' =&gt; string 'ACCOUNT HOLDER NAME' (length=11)
            'bank_username' =&gt; string 'username' (length=8)
            'bank_password' =&gt; string 'hehethisispassword' (length=18)
            'min_deposit' =&gt; string '400.00' (length=6)
            'max_deposit' =&gt; string '9999.99' (length=7)
            'daily_max_deposit' =&gt; string '25500.00' (length=8)
            'last_deposit' =&gt; string '2015-02-05 03:04:00' (length=19)
            'current_deposit' =&gt; string '0.00' (length=4)
            'created_by' =&gt; int 1
            'updated_by' =&gt; null
            'created_at' =&gt; string '2015-02-04 08:21:16' (length=19)
            'updated_at' =&gt; string '2015-02-04 08:21:16' (length=19)
            'deleted_at' =&gt; null
            'total_income' =&gt; string '1722.00' (length=7)
            'bank' =&gt; 
              array (size=7)
                ...
            'income' =&gt; string '1722.00' (length=7)
  </code></pre>