Why I i got different result in laravel query builder

Question

So i tried to convert this sql query to laravel query builder

select jenis_data.id_jenisdata, jenis_data.id_level, jenis_data.nama_data, detail_A.id_kecamatan, satuan.satuan,
    detail_A.tahun, detail_A.angka as tahun_2018, detail_B.angka as tahun_2019, opd.id_opd, detail_A.id_bidang, detail_A.id_detaildata,
    opd.wali_data, jenis_data.perkecamatan from jenis_data

    left join detail_data as detail_A on jenis_data.id_jenisdata = detail_A.id_jenisdata
    left join opd on detail_A.id_opd = opd.id_opd
    left join satuan on jenis_data.id_satuan = satuan.id_satuan
    left join detail_data as detail_B on detail_A.id_jenisdata = detail_B.id_jenisdata
    AND detail_A.tahun = 2018 
    AND detail_B.tahun = 2019
    group by jenis_data.id_jenisdata, detail_A.id_kecamatan

and i came up with this query builder

DB::table('jenis_data')
    ->select('jenis_data.id_jenisdata','jenis_data.id_level','jenis_data.nama_data','detail_A.id_kecamatan','satuan.satuan',
    'detail_A.tahun','detail_A.angka as tahun_2018', 'detail_B.angka as tahun_2019', 'opd.id_opd', 'detail_A.id_bidang',
    'opd.wali_data','jenis_data.perkecamatan')
    
    ->leftJoin('detail_data as detail_A', 'jenis_data.id_jenisdata','=','detail_A.id_jenisdata')
    ->leftJoin('opd','detail_A.id_opd','=','opd.id_opd')
    ->leftJoin('satuan','jenis_data.id_satuan','=','satuan.id_satuan')
    ->leftJoin('detail_data as detail_B', 'detail_A.id_jenisdata', '=', 'detail_B.id_jenisdata')
    ->where('detail_A.tahun','2018')
    ->where('detail_B.tahun','2019')
    ->groupBy('jenis_data.id_jenisdata', 'detail_A.id_kecamatan')
    ->get();

the problem is i got different result, the result from laravel query builder is fewer than the sql query. Its like im doing inner join with the query builder rather than left join.

result from sql query

result from query builder

Alex Alvarado · Accepted Answer · 2020-11-05T22:05:33.973

0

In your first query, there is no "where" clause.

In query builder...

->leftJoin('detail_data as detail_B', function ($join) {
    $join->on('detail_A.id_jenisdata', '=', 'detail_B.id_jenisdata')
        ->where('detail_A.tahun','2018')
        ->where('detail_B.tahun','2019');
})

edited Nov 05 '20 at 22:05

answered Nov 05 '20 at 21:40

Alex Alvarado

131
3

i've read somewhere that the AND condition for laravel query builder is using WHERE clause – Qori H Nov 05 '20 at 21:53
1

The problem is in SQL query, not the query builder. – Alex Alvarado Nov 05 '20 at 21:56
thank you.. it works :D what is that? can i get the keyword for future reference? – Qori H Nov 05 '20 at 22:17
look: https://laravel.com/docs/8.x/queries#advanced-join-clauses – Alex Alvarado Nov 05 '20 at 22:19

Why I i got different result in laravel query builder

1 Answers1