Using leftJoin() inside with() in laravel query builder

Question

I'm working on an Accounting software. I have a 3 tables

chart_master: contains all accounts

chart_types: contains the account types

bank_accounts: contains bank accounts (those bank accounts are also in chart_master

I want to convert the following sql query, which selects all accounts in chart_master as well as account_types which connect chart_master with chart_types and then excludes records in bank_accounts from the retrieved records, into laravel query builder. The query:

SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id
        FROM (chart_master chart, chart_types type) "
        ."LEFT JOIN bank_accounts acc "
        ."ON chart.account_code=acc.account_code
            WHERE acc.account_code  IS NULL
        AND chart.account_type=type.id

I was able to select the accounts while excluding the bank_accounts using the following:

    \App\Account::leftJoin('bank_accounts', 'chart_master.account_code', '=', 'bank_accounts.account_code')
->whereNull('bank_accounts.account_code')->get()

but that doesn't achieve what I want which is to retrieve the relationship between chart_master and char_types at the same time. Which I thought would be achieved by the following

 \App\AccountType::with(['accounts' => function($query){
        $query->leftJoin('bank_accounts', 'chart_master.account_code', '=', 'bank_accounts.account_code')
        ->whereNull('bank_accounts.account_code');
     }])->get();

That returned the account_types but didn't return any of the accounts

I tried to get the query log using DB::enableQueryLog() and that was the output

Array ( [0] => Array ( [query] => select * from `chart_master` left join `bank_accounts` on `chart_master`.`account_code` = `bank_accounts`.`account_code` where `bank_accounts`.`account_code` is null [bindings] => Array ( ) [time] => 1.66 ) [1] => Array ( [query] => select * from `chart_types` [bindings] => Array ( ) [time] => 1.03 ) [2] => Array ( [query] => select * from `chart_master` left join `bank_accounts` on `chart_master`.`account_code` = `bank_accounts`.`account_code` where `chart_master`.`account_type` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and `bank_accounts`.`account_code` is null [bindings] => Array ( [0] => 11 [1] => 12 [2] => 21 [3] => 22 [4] => 31 [5] => 32 [6] => 33 [7] => 38 [8] => 39 [9] => 61 [10] => 1101 [11] => 1102 [12] => 1103 [13] => 1104 [14] => 1105 [15] => 1201 [16] => 1202 [17] => 1203 [18] => 1209 [19] => 2101 [20] => 2102 [21] => 2103 [22] => 2104 [23] => 2105 [24] => 2106 [25] => 2107 [26] => 2108 [27] => 2109 [28] => 2110 [29] => 2111 [30] => 2112 [31] => 2201 [32] => 2202 [33] => 2203 [34] => 2204 [35] => 2205 [36] => 2206 [37] => 3101 [38] => 3102 [39] => 3201 [40] => 3301 [41] => 3302 [42] => 3303 [43] => 3304 [44] => 3305 [45] => 3801 [46] => 3901 [47] => 4101 [48] => 4201 [49] => 4202 [50] => 4203 [51] => 4204 [52] => 4205 [53] => 4209 [54] => 4301 [55] => 4401 [56] => 5101 [57] => 5102 [58] => 5201 [59] => 5202 [60] => 12011 [61] => 12012 [62] => 12013 [63] => 12091 [64] => 12092 [65] => 12093 [66] => 12094 [67] => 110301 [68] => 110302 [69] => 110303 [70] => 110304 [71] => 110305 [72] => 120110 [73] => 120120 [74] => 120130 [75] => 120910 [76] => 120920 [77] => 120930 [78] => 120940 [79] => 210101 ) [time] => 2.57 ) )

The query seems to have nothing wrong with it still doesn't retrieve the accounts

That log data contains only the two Query Builder queries

\App\Account.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Account extends Model
{
    protected $table = "chart_master";


    public function type()
    {
        return $this->belongsTo('App\AccountType', 'account_type');
    }
}

\App\AccountType.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class AccountType extends Model
{
    protected $table = "chart_types";

    public function accounts()
    {
        return $this->hasMany('App\Account', 'account_type');
    }
}

7asobate- · Accepted Answer · 2018-08-26T00:43:01.863

Welp, seems like I was over-complicating it for myself trying to perform a query that is exactly the same as the original and didn't see an alternative solution to such a simple problem which is excluding a bunch of records.

Anyway here's the code that achieves the desired action:

$accountTypesWithAccountsExcludingBankAccounts 
     = \App\AccountType::has('accounts')->with(['accounts' => function($query){
        $query->whereNotIn('chart_master.account_code', \App\BankAccount::all()->pluck('account_code') );
     }])->get();

I still don't know why the code I originally wrote didn't work though.

Using leftJoin() inside with() in laravel query builder

1 Answers1