laravel where raw automaticaly add is null-CodePudding

i have this code where i need to join transactions with transaction_sell_lines and transactions with purchase_lines. Then purchase_lines and transaction_sell_lines both has variation_id. I need to join those variation_id to variations table.

 Transaction::leftJoin('purchase_lines as pl', 'transactions.id', '=', 'pl.transaction_id')
 ->leftJoin('transaction_sell_lines as sl', 'transactions.id', '=', 'sl.transaction_id')
 ->join('variations as v', function ($query) {
     $query->where(DB::raw("CONCAT(COALESCE(sl.variation_id, ''), COALESCE(pl.variation_id, ''))"));
 }, '=', 'v.id')

and when i checked the query using DB::enableQueryLog(); for some reason laravel automatically add is null even though i did not mention it anywhere. i could use whereNotNull() but the result still are not the same. the code could be seen below

from `transactions` left join `purchase_lines` as `pl` on `transactions`.`id` = `pl`.`transaction_id` 
left join `transaction_sell_lines` as `sl` on `transactions`.`id` = `sl`.`transaction_id` 
inner join `variations` as `v` on CONCAT(COALESCE(sl.variation_id, ''), COALESCE(pl.variation_id, '')) is null

how do i achieve this sql

from transactions LEFT JOIN purchase_lines as pl on pl.transaction_id = transactions.id 
LEFT JOIN transaction_sell_lines as sl ON sl.transaction_id = transactions.id
LEFT JOIN variations as v on v.id = CONCAT(COALESCE(sl.variation_id,''),COALESCE(pl.variation_id,''))

CodePudding user response：

I would suggest that you replace

$query->where(DB::raw("...")

with the whereRaw-function instead:

 Transaction::leftJoin('purchase_lines as pl', 'transactions.id', '=', 'pl.transaction_id')
 ->leftJoin('transaction_sell_lines as sl', 'transactions.id', '=', 'sl.transaction_id')
 ->join('variations as v', function ($query) {
     $query->whereRaw("CONCAT(COALESCE(sl.variation_id, ''), COALESCE(pl.variation_id, ''))");
 }, '=', 'v.id')

CodePudding user response：

you are using Advanced Join Clauses the wrong way ...

join with closure should take a Illuminate\Database\Query\JoinClause instance which allows you to specify constraints on the "join" clause, so the constraints should be in that function:

you can replace:

 ->join('variations as v', function ($query) {
     $query->where(DB::raw("CONCAT(COALESCE(sl.variation_id, ''), COALESCE(pl.variation_id, ''))"));
 }, '=', 'v.id')

with:

 ->join('variations as v', function ($query) {
     $query->where(DB::raw("CONCAT(COALESCE(sl.variation_id, ''), COALESCE(pl.variation_id, ''))=v.id"));
 })