i want to make where clause inside to select DB::raw in laravel-CodePudding

i want to make where clause inside to select DB::raw in laravel i have two tables name as biodata_wni dan demographics , i want to count data where collom akta_kwn in biodata_wni has value 1 dan also count akta_kwn in biodata_wni has value 2

can anyone have to case like this ?

 $kec= $request->kecamatan;

 $aktakawin= DB::table('biodata_wnis')
        ->join('demographics', 'biodata_wnis.nik','=','demographics.nik')
        ->where('demographics.nama_kec','=',$kec)
        ->where('biodata_wnis.semester','=',$smstr)
        ->select(
            DB::raw("count(biodata_wnis.akta_kwn) WHERE biodata_wnis.akta_kwn = 1 as jml_lk"),
            DB::raw("count(biodata_wnis.akta_kwn) WHERE biodata_wnis.akta_kwn = 2 as jml_pr")
            , 'demographics.nama_kel')
        ->groupBy('demographics.nama_kel as name')
        ->get();

I want to make a result like this

Illuminate\Support\Collection {#1321 ▼
  #items: array:5 [▶
    0 => {#1329 ▶
       "jml_lk": 21
       "jml_pr": 1
       "name": "CARANGSARI"
    }
    1 => {#1323 ▶
      "jml_lk": 21
      "jml_pr": 1
      "name": "CESTSA"
    }

CodePudding user response：

Its the SQL syntax that's the problem, you haven't have a WHERE that applies to count.

What you can do is:

    ->select(
        DB::raw("sum(biodata_wnis.akta_kwn = 1) as jml_lk"),
        DB::raw("sum(biodata_wnis.akta_kwn = 2) as jml_pr")

There the biodata_wnis.akta_kwn = 1 is a 0 or 1 expression depending if its true or not. suming them up results in the count for that particular item.