laravel Nested Query Builder

please help by formatting your code

$start = Carbon::parse($request->from)->format('Y-m-d');

$end = Carbon::parse($request->to)->format('Y-m-d');

$report_info = PatientDetails::with('booking','masters')
                ->whereHas('booking', function($query) use ($start,$end){
                    $query->with('patientDetails')->whereDate('created_at','>=',$start)
->WhereDate('created_at','<=',$end);
                })->whereIn('status', ["1","4"])->get();

should be ok, but my version would be

$start = Carbon::parse($request->from);

$end = Carbon::parse($request->to);

// leave dates as carbon instances

// dd($start, $end);        // check these are coming through 

$report_info = PatientDetails::with('booking','masters')
                ->whereHas('booking', function($query) use ($start,$end){
                    $query->whereDate('created_at','>=',$start)
->WhereDate('created_at','<=',$end);
                })->whereIn('status', ["1","4"])->get();

Not working.

$report_info = PatientDetails::with('masters')
                    ->with(['booking' => function($query) use ($start,$end){
                             $query->whereDate('booking_details.checkIn','>=',$start)->WhereDate('booking_details.checkIn','<=',$end);
                        }])->whereIn('status', ["1","4"])->get();

So, booking_details is the table name and model is booking.

Still not working

By "not working" do you mean you're getting errors, getting no data, or getting some data, but not the data you were expecting?

no errors. getting some data, but not the data i was expecting.. So there is a match with that particular date sent. it is returning empty array

Ok so the problem was with the status i passed.

$report_info = PatientDetails::with('masters')
                    ->with(['booking' => function($query) use ($start,$end){
                             $query->whereDate('booking_details.checkIn','>=',$start)->WhereDate('booking_details.checkIn','<=',$end);
                        }])->whereIn('status', ["1","4"])->get();

FInally woking, Thank You

PatientDetails::with('masters')
                        ->with(['booking' => function($query) use ($start,$end){
                            $query->whereDate('booking_details.checkIn','>=',$start)->WhereDate('booking_details.checkIn','<=',$end);
                        }])->whereIn('status', ["0","2"])->toSql();
        print_r($report_info);

output

select * from `patient_details` where `status` in (?, ?)   

why is this happening?

Its what you asked for.

The first query will be to get from PatientDetails where the status is 0 or 2 (code smelly 'magic numbers')

The second query will be to get Bookings that are belonging to the result of the first query and having the right date range

Eloquent will then merge the results

Install Laravel debugbar rather than using toSQL

Okie. But the problem with the query is it fetches null value to booking. I want only those records whose booking lies between check in from and to.

Cyrrently it is returning all the records with booking null if it does not match the checkin from and to date.

Because you apply the date range to the with statement so you load all PatientDetails, but only eager load relations with the date range

If you want to restrict PatientDetails according to booking then you need to use whereHas as you had before

$report_info = PatientDetails::with('booking','masters')
                ->whereHas('booking', function($query) use ($start,$end){
                    $query->whereDate('checkIn','>=',$start)
                    ->WhereDate('checkIn','<=',$end);
                })->whereIn('status', ["0","2"])->get();

This returns empty array even after having values.

check your dates

check the checkIn column has valid values between start and end

check the checkIn column is spelled correctly and is a date or timestamp column type

Dump the sqland check what query is being run..

There are loads of things you can do to help yourself rather than asking and waiting