How to use if-else on where clause

Question

I'm trying to figure out how to use if-else on a where clause when lingkup equals "desa." Where clause only status = valid but when lingkup is "kecamatan" where clause status = valid and status_kecamatan = valid.

$results = DB::table('tb_lapak')
    ->select('tb_surat.lingkup', 'tb_lapak.*')
    ->join('tb_surat', 'tb_lapak.kode_surat', '=', 'tb_surat.kode_surat')
    ->when('lingkup' === 'desa', function ($query) {
        return $query->where('status', '=', 'valid');
    })
    ->when('lingkup' === 'kecamatan', function ($query) {
        return $query->where('status', '=', 'valid');
        return $query->where('status_kecamatan', '=', 'valid');
    });

here is the table

CREATE TABLE `tb_lapak` (
  `kode_lapak` int(11) NOT NULL,
  `kode_surat` int(11) NOT NULL,
  `tanggal` char(35) NOT NULL,
  `nik` char(35) NOT NULL,
  `keterangan` text NOT NULL,
  `status` enum('proses','valid') NOT NULL,
  `status_kecamatan` enum('proses','valid') NOT NULL,
  `create_at` timestamp NULL DEFAULT current_timestamp(),
  `kode_lingkup` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tb_surat` (
  `kode_surat` int(11) NOT NULL,
  `lingkup` enum('kecamatan','desa') NOT NULL,
  `nama_surat` varchar(225) NOT NULL,
  `output` varchar(225) DEFAULT NULL,
  `aktif` enum('Y','T') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

user_16311676 · Answer

This may not need sample data, it is just a logical question

where (lingkup = 'desa' and status = 'valid')
   or (lingkup = 'kecamatan' and status = 'valid' and status_kecamatan = 'valid')
   or (lingkup not in ('desa', 'kecamatan'))

How to use if-else on where clause

1 Answers