How to wrap mutliple queries into a single where clause in laravel

Question

I want to fetch records from a table pasien_polis where polyclinic_id matches the id of the polyclinics table.

I can achieve that by doing this:

    $polyclinic = Polyclinic::findOrFail($id);
    $pasienpoli = Polyclinic::find($id)->PasienPoli;

the next thing I want to filter is the created_at records, I only want to fetch records created today. This query works well:

    $pasienpoli = DB::table('pasien_polis')->whereDate('created_at', '=', \Carbon\Carbon::today()->toDateString())->get();

the problem came up when I wanted to combine those two filters into a single where clause.

I have tried the following but it returns NULL:

$polyclinic = Polyclinic::findOrFail($id);
$match = Polyclinic::find($id)->PasienPoli;

$pasienpoli = DB::table('pasien_polis')->where([
                     ['polyclinic_id', '=', '$match'],
                     ['created_at', '=', \Carbon\Carbon::today()->toDateString()]
                                               ])->get();

Any help please?

UPDATE:

Answer 1

I think you can try this :

    $pasienpoli = DB::table('pasien_polis')
                        ->join('polyclinics','polyclinics.id','=','pasien_polis.polyclinic_id')
                        ->where([
                         ['pasien_polis.polyclinic_id', '=', $id],
                         ['pasien_polis.created_at', '=', \Carbon\Carbon::today()->toDateString()]])
                       ->get();

Hope this help for you !