Query Builder OR and IS NULL in Laravel 9

FebryanAkhdani
April 4, 2023
202 views
1 vote
2 Answers

I have a query like this

SELECT ruangans.nama_ruangan, beds.id AS idBed, beds.nomor_bed, pasiens.nama, pasiens.status
FROM `beds` JOIN ruangans ON beds.id_ruangan = ruangans.id
LEFT JOIN pasiens ON beds.id = pasiens.id_bed
WHERE pasiens.status != 'Sudah Rawat Inap' OR pasiens.status IS NULL;

I’m still new in Laravel Query Builder, my question is how to convert the SQL query to Query Builder, especially at the OR and IS NULL operation. Thank You

Answers

- Sambhav
- April 5, 2023 at 6:57 am
- 0 votes
0
```
DB::table("beds")
->join("ruangans", "beds.id_ruangan", "=", "ruangans.id")
->leftJoin("pasiens", "beds.id", "=", "pasiens.id_bed")
->select("ruangans.nama_ruangan", "beds.id as idbed", "beds.nomor_bed", "pasiens.nama", "pasiens.status")
->where("pasiens.status", "!=", 'Sudah Rawat Inap')
->orWhereNull("pasiens.status")
->get();
```
Laravel offers several variation of where methods to add "where" clauses to your queries. You can find them here: https://laravel.com/docs/10.x/queries#basic-where-clauses
Login or Signup to reply.

You can use

$query = DB::table('beds')
    ->join('ruangans', 'beds.id_ruangan', '=', 'ruangans.id')
    ->leftJoin('pasiens', 'beds.id', '=', 'pasiens.id_bed')
    ->select(
        'ruangans.nama_ruangan',
        'beds.id AS idBed',
        'beds.nomor_bed',
        'pasiens.nama',
        'pasiens.status'
    )
    ->where(function ($query) {
        $query->where('pasiens.status', '!=', 'Sudah Rawat Inap')
              ->orWhereNull('pasiens.status');
    })
    ->get();

As you know we use where to filter the data. In this, I’ve used a sub-query mechanism to filter the data within the same pasiens table.