Query in laravel returns empty response

Question

I am tying to retrieve data from my data base using leftJoin in laravel. I have two table name users and appointments in my appointments table there is a field name doctor_id that holds the doctor id related to the id in users. both the id value is same. I need the name of doctors from users table and appointment_date , status from appointments table if the column id value in users table matches column appointments value in appointments table with a where clause where patient_id = $patientID. But when I execute my query it returns empty response.

code

$patientID = $request->patientID;
$prevAppointments = DB::table('appointments')
  ->leftJoin('users', 'appointments.doctor_id', '=', 'users.id')
  ->select(
    'users.first_name',
    'users.last_name',
    'appointments.appointment_date',
    'appointments.status'
  )
  ->where('appointments.patient_id', $patientID)
  ->get();
return($prevAppointments);

user table

user table

appointments table

appointments table

First, did you run query from your database. Do you've results there? Use sqlfiddle.com to share some sample data. — jones
– jones, Commented Sep 21, 2019 at 15:31

Rashed Hasan · Accepted Answer · 2019-09-21 15:48:37Z

2

Try this -

$patientID = $request->patientID;
$prevAppointments = DB::table('appointments')
                        ->leftJoin('users', 'appointments.doctor_id', '=', 'users.id')
                        ->select('users.first_name','users.last_name','appointments.patient_id', 'appointments.appointment_date','appointments.status')
                        ->where('appointments.patient_id',$patientID)
                        ->get();
return($prevAppointments);

answered Sep 21, 2019 at 15:48

Rashed Hasan

3,77111 gold badges43 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ajmal-Hossain Over a year ago

Still returns blank response

Rashed Hasan Over a year ago

check your $request->patientID; and doctor_id have both for users and appointments table. I tested it already and it's working.

Ajmal-Hossain Over a year ago

It worked. i am sorry my appointments table was blank.Thank you ;)

Rashed Hasan Over a year ago

@Ajmal-Hossain Another important thing is you should practice one of the great feature of Laravel - Eloquent. It's give you better performance and less error and easy process what is already said "Hamid Shariati". Please have a look - laravel.com/docs/5.8/eloquent

Hamid Shariati · Accepted Answer · 2019-09-21 16:07:27Z

1

create model with this command (if you don't have any Appointment Model): inside root of your project type:

php artisan make:model Appointment

inside the created file write these:

protected $fillable=['patient_id','doctor_id','appointment_date','status'];

public function doctor(){
    $this->belongsTo(User::class,'doctor_id');
}

now you can use this Eloquent Query: first add Appointment class:

 Use App\Appointment;

then use this in method:

 $prevAppointments = Appointment->where('patient_id','=',$patientID)->first();

now you have a collection that contain appointment with doctor properties. for more details on Eloquent ORM and Laravel Collections follow this link: https://hamidshariati.ir/most-important-articles-to-learn-laravel/

answered Sep 21, 2019 at 16:07

Hamid Shariati

5996 silver badges22 bronze badges

4 Comments

Ajmal-Hossain Over a year ago

you did not get my question

Hamid Shariati Over a year ago

if you use eloquent laws and if the table have data with that patient_id you got result. leftjoin and query that you use is more complicated and make more error.

Hamid Shariati Over a year ago

you can test your query with type: php artisan tinker

Hamid Shariati Over a year ago

also you can give constant to patient_id to sure if your query is right where you know patient_id has appointment and doctor_id is not null.

Collectives™ on Stack Overflow

Query in laravel returns empty response

2 Answers 2

4 Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Related