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:

Please show your table structure for both tables (pasien_polis, polyclinics) — AddWeb Solution Pvt Ltd, Jun 29 '17 at 10:57
I have updated the post and posted the table structures. @AddWebSolutionPvtLtd — Daddi Al Amoudi, Jun 29 '17 at 11:10

score 1 · Accepted Answer · answered Jun 29 '17 at 11:17

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 !

answered Jun 29 '17 at 11:17

AddWeb Solution Pvt Ltd

21,025
5
26
57

still get the same result. i tried to change this polyclinics.id to id, i got mysql error. in aptana it says syntax error, but i think that's not the real problem. I feel i have set the ralations corectly but still don't get it working properly. – Daddi Al Amoudi Jun 29 '17 at 11:45
I accepted this answer because i got it working by modifying this answer. thank you very much. – Daddi Al Amoudi Jun 29 '17 at 15:06

score 0 · Answer 2 · answered Jun 29 '17 at 14:54

SOLVED by modifying @AddWeb Solution Pvt's answer (thanks a bunch for that).
here is my final working query:

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

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

2 Answers2