CODEWITHSUNDEEP
phpmysqllaravellaravel-query-builder
Ajmal-Hossain
Ajmal-Hossain

Reputation: 215

Query in laravel returns empty response

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

Upvotes: 1

Views: 1616

Answers (2)

Hamid Shariati
Hamid Shariati

Reputation: 599

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/

Upvotes: 1

Rashed Hasan
Rashed Hasan

Reputation: 3751

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);

Upvotes: 2

Related Questions