laravel eloquent JOIN ON 多个条件

laravel eloquent JOIN ON multiple condition

提问by user259752

i have a table call bank, which store the country bank name, code, etc

我有一个表调用银行，它存储国家银行名称、代码等

i have a table call site_banks, which store the bank account, bank holder name and foreign bank_idwhich refer to bank

我有一个名为site_banks的表，它存储银行账户、银行持有人姓名和外国银行ID指的是银行

i have a table call deposit_records, which store deposit time, deposit receipt, status, etc and foreign bank_idwhich refer to site_banks

我有一个表叫deposit_records，它存储存款时间，存款收据，状态等和引用site_banks 的外国银行 ID

for deposit_records, the status field, value 1 mean successfully

对于deposit_records，状态字段，值 1 表示成功

now i want to calculate the total income for each site_banks

现在我想计算每个site_banks的总收入

but i don't want each row site_bankscall one query to count, this will have a lots of sql query call

但我不希望每一行site_banks调用一个查询来计数，这将有很多 sql 查询调用

so i think i can use the join, below is the site_banksmodel file, the scopeGroupAllSiteBanksByBranchis where i want to collect all site_banks, join with SUM, return in array

所以我想我可以使用join，下面是site_banks模型文件，scopeGroupAllSiteBanksByBranch是我想收集所有 site_banks 的地方，加入 SUM，返回数组

but even i have about 200 site_banksrows, a lots of deposit_recordsrows which is status = '1', but the scopeGroupAllSiteBanksByBranchwill 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.

但即使我有大约 200 个site_banks行，很多deposit_records行是status = '1'，但scopeGroupAllSiteBanksByBranch将始终只返回一行，我不明白为什么，我复制了 sql 查询以在 mysql 中运行，是相同的结果，只有 1 行返回。

<?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' => 'required|exists:banks,id',
        'bank_name' => 'required|min:1',
        'bank_account' => 'required|min:1|numberonly',
        'bank_holder' => 'required|min:1',
        'bank_username' => 'required|min:1',
        'bank_password' => 'required|min:1',
        'min_deposit' => 'required|fund',
        'max_deposit' => 'required|fund',
        'daily_max_deposit' => 'required|fund',
    );

    public function bank() {
        return $this->belongsTo('Banks', 'bank_id');
    }

    public function transactions() {
        return $this->hasMany('Deposit', 'bank_id', 'id');
    }

    public function transactionsDone() {
        return $this->hasMany('Deposit', 'bank_id', 'id')->where('status', '=', 1);
    }

    public function transactionsBanned() {
        return $this->hasMany('Deposit', 'bank_id', 'id')->where('status', '=', 2);
    }

    public function transactionsPending() {
        return $this->hasMany('Deposit', 'bank_id', 'id')->where('status', '=', 0);
    }

    public function setBankPasswordAttribute($value) {
        $this->attributes['bank_password'] = Crypt::encrypt($value);
    }

    public function getBankPasswordAttribute() {
        return Crypt::decrypt($this->attributes['bank_password']);
    }

    public function scopeGetAvailableBanks($query) {
        $query->where(DB::raw("(current_deposit < 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()) && current_deposit < daily_max_deposit)"));
        $query->groupBy('bank_id')->orderBy('id', 'ASC');
    }

    public function scopeGroupAllSiteBanksByBranch($query) {
        $bank = \SiteBanks::withTrashed()
            ->leftJoin('deposit_records', function($q) {
                $q->on('deposit_records.bank_id', '=', 'site_banks.id');
                $q->where('deposit_records.status', '=', 1, 'and');
            })
            ->select(array('site_banks.*', DB::raw('SUM(`deposit_records`.`deposit_amount`) AS `total_income`')))
            ->get();
//        $bank = \SiteBanks::withTrashed()->get();
        $bank->load('bank');

        $banks = array();
        foreach($bank as $key => $var) {
            $arr = array();

            $arr = $var->toArray();
            $arr['income'] = $var->total_income;
            $banks[$var->bank->bank_name][] = $arr;
        }

        return $banks;
    }

}

SQL Query

SQL查询

select site_banks.*, SUM(deposit_records.deposit_amount) AS total_incomefrom site_banksleft join deposit_recordson deposit_records.bank_id= site_banks.idand deposit_records.status= '1'

select site_banks.*, SUM( deposit_records. deposit_amount) AS total_incomefrom site_banksleft join deposit_recordson deposit_records。bank_id= site_banks. id和 deposit_records。status= '1'

var_dumpthe result from scopeGroupAllBanksByBranch

var_dump来自scopeGroupAllBanksByBranch的结果

array (size=1)
  'BANKNAME' => 
    array (size=1)
      0 => 
        array (size=20)
          'id' => int 1
          'bank_id' => int 4
          'bank_name' => string 'BANKNAME' (length=6)
          'bank_account' => string '123456789' (length=9)
          'bank_holder' => string 'ACCOUNT HOLDER NAME' (length=11)
          'bank_username' => string 'username' (length=8)
          'bank_password' => string 'hehethisispassword' (length=18)
          'min_deposit' => string '400.00' (length=6)
          'max_deposit' => string '9999.99' (length=7)
          'daily_max_deposit' => string '25500.00' (length=8)
          'last_deposit' => string '2015-02-05 03:04:00' (length=19)
          'current_deposit' => string '0.00' (length=4)
          'created_by' => int 1
          'updated_by' => null
          'created_at' => string '2015-02-04 08:21:16' (length=19)
          'updated_at' => string '2015-02-04 08:21:16' (length=19)
          'deleted_at' => null
          'total_income' => string '1722.00' (length=7)
          'bank' => 
            array (size=7)
              ...
          'income' => string '1722.00' (length=7)

回答by Majbah Habib

You can solved your problem by following the code

您可以按照代码解决您的问题

SQL query

SQL查询

LEFT JOIN bookings  
           ON rooms.id = bookings.room_type_id AND (bookings.arrival = ? OR bookings.departure = ? )

Laravel join with multiple condition

Laravel 加入多个条件

 ->join('bookings', function($join) use ($key1, $key2)
{
    $join->on('rooms.id', '=', 'bookings.room_type_id');
    $join->on(function($query) use ($key1, $key2)
            {
             $query->on('bookings.arrival', '=', $key1);
             $query->orOn('departure', '=',$key2);
            });

})

回答by user259752

public function scopeGroupAllSiteBanksByBranch($query) {
        $bank = \SiteBanks::withTrashed()
            ->leftJoin('deposit_records', function($q) {
                $q->on('deposit_records.bank_id', '=', 'site_banks.id');
                $q->where('deposit_records.status', '=', 1, 'and');
            })
            ->select(array('site_banks.*', DB::raw('SUM(`deposit_records`.`deposit_amount`) AS `total_income`')))
            ->groupBy('site_banks.id')
            ->get();

i found the solutions, with groupBy('site_banks.id')then is all set.

我找到了解决方案，然后groupBy('site_banks.id')就设置好了。