php - leftJoin not working properly in laravel eloquent?

Question

I am writing on one query with 2 tables in laravel. i am getting proper response when there is data in only parent table. when i trying to add data in child table my query result not giving me proper response.

parent table : CASES (caseId) <- primary key child table : NOTICES (noticeId) <- primary key

dummy data in CASES:

caseId | caseName | caseNumber | customerId 
1      | case1    | 1234       | 90
2      | case2    | 890        | 90
3      | case3    | 8900       | 90

dummy data in NOTICES:

noticeId | noticeName | noticeData  | caseId | custId
1        | notice1    | {no:1;no1:2}| 1      | 90

current output :

{
  caseId : 1,
  caseName : case1,
  caseNumber : 1234,
  noticeId : 1,
  noticeName : notice1,
  noticeData : {no:1;no1:2},
},
{
  caseId : 2,
  caseName : case2,
  caseNumber : 890,
  noticeId : 1,
  noticeName : notice1,
  noticeData : {no:1;no1:2},
},
{
  caseId : 3,
  caseName : case3,
  caseNumber : 8900,
  noticeId : 1,
  noticeName : notice1,
  noticeData : {no:1;no1:2},
},

Expected Output :

{
      caseId : 1,
      caseName : case1,
      caseNumber : 1234,
      noticeId : 1,
      noticeName : notice1,
      noticeData : {no:1;no1:2},
    },
    {
      caseId : 2,
      caseName : case2,
      caseNumber : 890,
      noticeId : null,
      noticeName : null,
      noticeData : null,
    },
    {
      caseId : 3,
      caseName : case3,
      caseNumber : 8900,
      noticeId : null,
      noticeName : null,
      noticeData : null,
    },

My query :

$data =DB::table('CASES')
                ->select('CASES.id','CASES.caseName','CASES.caseNumber','NOTICES.noticeName', 'NOTICES.noticeData')
                ->leftjoin('NOTICES', 'CASES.caseId', '=', 'NOTICES.caseId')
                ->leftjoin('NOTICES as NT', 'CASES.customerId', '=', 'NT.custId')
                ->get()->toArray();

question from:https://stackoverflow.com/questions/66047066/leftjoin-not-working-properly-in-laravel-eloquent

Answer 1

to join by more than a column you can use advanced join:

 $data = DB::table('CASES')
            ->select('CASES.id', 'CASES.caseName', 'CASES.caseNumber', 'NOTICES.noticeName', 'NOTICES.noticeData')
            ->leftjoin('NOTICES', function ($join) {
                $join->on('CASES.caseId', '=', 'NOTICES.caseId')->on('CASES.customerId', '=', 'NOTICES.custId');
            })->get()->toArray();

please note that you can use 'orOn' instead of 'on' in the join clause, it depends on the results you want.