Returns all columns even if the data is 0 or null

please help me fix my code.

I have an initial function is as follows:

public function chart(){
    DB::enableQueryLog();
    $chartKategori = DB::table("ms_pengaduan_kategori as mpk")
        ->leftJoin('tr_pengaduan as t', 't.kategori_id', 'mpk.kategori_id')
        ->select(DB::raw('count(t.pengaduan_id) jumlah'), "mpk.nama_kategori", "mpk.kategori_id")
        ->groupBy(["kategori_id", 'nama_kategori'])
        ->orderBy('kategori_id')
        ->get();
    $faqs = [DB::getQueryLog(), $chartKategori];
    return response()->json($faqs);
}

The result is:

[
   [
      {
         "query":"select count(t.pengaduan_id) jumlah, `mpk`.`nama_kategori`, `mpk`.`kategori_id` from `ms_pengaduan_kategori` as `mpk` left join `tr_pengaduan` as `t` on `t`.`kategori_id` = `mpk`.`kategori_id` group by `kategori_id`, `nama_kategori` order by `kategori_id` asc",
         "bindings":[
            
         ],
         "time":1.18
      }
   ],
   [
      {
         "jumlah":6,
         "nama_kategori":"Disiplin Pegawai",
         "kategori_id":1
      },
      {
         "jumlah":2,
         "nama_kategori":"Sengketa",
         "kategori_id":2
      },
      {
         "jumlah":0,
         "nama_kategori":"Konflik Pertanahan",
         "kategori_id":3
      },
      {
         "jumlah":1,
         "nama_kategori":"Informasi dan Pelayanan Pertanahan",
         "kategori_id":4
      }
   ]
]

Then I tried to add a "whereIn" condition to the function because I just want to get the amount of data based on the logged in User ID ($posId).

public function chart(){
    DB::enableQueryLog();
    $posId = Auth::user()->posisi_id;
    $chartKategori = DB::table("ms_pengaduan_kategori as mpk")
        ->leftJoin('tr_pengaduan as t', 't.kategori_id', 'mpk.kategori_id')
        ->select(DB::raw('count(t.pengaduan_id) jumlah'), "mpk.nama_kategori", "mpk.kategori_id")
        ->whereIn('t.posisi_id', function ($q) use ($posId) {
            return $q->select(DB::raw('posisi_id'))
                ->from('ms_pengaduan_posisi as mpp')
                ->where('mpp.posisi_id', $posId)
                ->orWhere('mpp.parent_id', $posId);
        })
        ->groupBy(["kategori_id", 'nama_kategori'])
        ->orderBy('kategori_id')
        ->get();
    $faqs = [DB::getQueryLog(), $chartKategori];
    return response()->json($faqs);
}

The result is:

[
   [
      {
         "query":"select count(t.pengaduan_id) jumlah, `mpk`.`nama_kategori`, `mpk`.`kategori_id` from `ms_pengaduan_kategori` as `mpk` left join `tr_pengaduan` as `t` on `t`.`kategori_id` = `mpk`.`kategori_id` where `t`.`posisi_id` in (select posisi_id from `ms_pengaduan_posisi` as `mpp` where `mpp`.`posisi_id` = ? or `mpp`.`parent_id` = ?) group by `kategori_id`, `nama_kategori` order by `kategori_id` asc",
         "bindings":[
            2,
            2
         ],
         "time":1.25
      }
   ],
   [
      {
         "jumlah":3,
         "nama_kategori":"Disiplin Pegawai",
         "kategori_id":1
      },
      {
         "jumlah":1,
         "nama_kategori":"Sengketa",
         "kategori_id":2
      }
   ]
]

Why is the result only columns that have values?

What I expected was like :

[
   [
      {
         "query":"select count(t.pengaduan_id) jumlah, `mpk`.`nama_kategori`, `mpk`.`kategori_id` from `ms_pengaduan_kategori` as `mpk` left join `tr_pengaduan` as `t` on `t`.`kategori_id` = `mpk`.`kategori_id` where `t`.`posisi_id` in (select posisi_id from `ms_pengaduan_posisi` as `mpp` where `mpp`.`posisi_id` = ? or `mpp`.`parent_id` = ?) group by `kategori_id`, `nama_kategori` order by `kategori_id` asc",
         "bindings":[
            2,
            2
         ],
         "time":1.25
      }
   ],
   [
      {
         "jumlah":3,
         "nama_kategori":"Disiplin Pegawai",
         "kategori_id":1
      },
      {
         "jumlah":1,
         "nama_kategori":"Sengketa",
         "kategori_id":2
      },
      {
         "jumlah":0,
         "nama_kategori":"Konflik Pertanahan",
         "kategori_id":3
      },
      {
         "jumlah":0,
         "nama_kategori":"Informasi dan Pelayanan Pertanahan",
         "kategori_id":4
      }
   ]
]

I don't know which part is wrong, I've tried to change count(t.pengaduan_id) jumlah to IFNULL(count(t.pengaduan_id),0) jumlah but the result I get is the same.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3f5URmR
via IFTTT