Selecting from multiple tables in laravel-CodePudding

please help.I have a test booking table that looks like this

Schema::create('test_bookings', function (Blueprint $table) {
        $table->unsignedInteger('RequestID');
        $table->string('bookingDate');
        $table->string('timeSlot');
        $table->unsignedInteger('nurse_id');
        $table->timestamps();
    });

and a tests table that looks like this

Schema::create('tests', function (Blueprint $table) {
        $table->unsignedInteger('RequestID');
        $table->unsignedInteger('patientID');
        $table->string('barcode');
        $table->string('temperature');
        $table->string('pressure');
        $table->string('oxygen');
        $table->unsignedInteger('nurseID');
        $table->timestamps();
    });

I want to show the RequestID,bookingDate,timeSlot, name and surname of the nurse only if the test_bookings RequestID is in tests table. This is my nurse table

Schema::create('nurses', function (Blueprint $table) {
        $table->unsignedInteger('nurseID');
        $table->string('name');
        $table->string('surname');
        $table->string('idNumber');
        $table->string('phone');
        $table->string('email');
        $table->unsignedInteger('suburb_id');
        $table->timestamps();


        $table->index('suburb_id');
    });

This is the code that i tried

$tests =  DB::table('tests')
                ->select('RequestID','bookingDate','timeSlot','name','surname')
                ->join('nurses','nurses.nurseID','test_bookings.nurse_id')
                ->join('test_bookings','test_bookings.RequestID','=','tests.RequestID')
                ->get();

CodePudding user response：

but when I join the tests table nothing is showing

that because you are using join clause that generate innerJoin statement, and to see the results you should use leftJoin

$tests =  DB::table('tests')
                ->select('RequestID','bookingDate','timeSlot','name','surname')
                ->leftJoin('nurses','nurses.nurseID','=','test_bookings.nurse_id')
                ->leftJoin('test_bookings','test_bookings.RequestID','=','tests.RequestID')
                ->get();

CodePudding user response：

Why you're not using ORM here, a simple one-to-one relationship can do the job perfectly. Here is an example:

class TestBooking extends Model {
 # Other code...
 public function nurse(){
    return $this->belongsTo(Nurse::class);
  }
}

class Test extends Model {
 # Other code...
 public function testBooking(){
    return $this->belongsTo(TestBooking::class, 'RequestID','RequestID');
  }
}

Now you can get all data like this:

 $tests = Test::with("testBooking","testBooking.nurse")->get();
 // and get data inside loop like this:
 $test->RequestID // to get request ID
 $test->testBooking->bookingDate // to get booking date
 $test->testBooking->timeSlot // to get timeSlot
 $test->testBooking->nurse->name // to get nurse name
 $test->testBooking->nurse->surname // to get nurse surename

To know more about relationships read documention.