'I want to make where clause inside to select DB::raw in laravel

I have two tables biodata_wni and demographics, I want to count data where column akta_kwn in biodata_wni has value 1 and also the count of akta_kwn in biodata_wni is 2.

Can anyone have to case like this ?

$smstr= $request->semester;  
$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 get 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"
    }
jquerymysqllaravelquery-builder


Solution 1:[1]

Its the SQL syntax that's the problem, there isn't a SQL syntax where a WHERE 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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions