I have two tables: patients and appts (appointments). There is a one:many relationship patient:appt. My goal is to return a collection of patients, and from the relation the dates of the patient's last and next appointments.
Problem / Issue
I'm getting inconsistent results. I've seeded the data so that each patient has appointments in the past and future, but when I use Eloquent to query (see 'Controller' below), I don't get a "lastappt". If I modify the query to search by the patient's id, I do get the lastappt.
The 'Patient' Model
class Patient extends Model
{
...
public function appts()
{
return $this->hasMany('App\Models\Appt');
}
public function lastappt()
{
$now = Carbon::now();
return $this
->hasMany('App\Models\Appt')
->select(['id', 'patient_id', 'appt_date_time'])
->where('appt_date_time', '<', $now)
->orderBy('appt_date_time', 'desc')
->take(1);
}
public function nextappt()
{
$now = Carbon::now();
return $this
->hasMany('App\Models\Appt')
->select(['id', 'patient_id', 'appt_date_time'])
->where('appt_date_time', '>', $now)
->orderBy('appt_date_time', 'asc')
->take(1);
}
}
The 'Appts' Migration
Schema::create('appts', function (Blueprint $table) {
$table->increments('id');
$table->integer('patient_id')->unsigned();
$table->integer('hospital_id')->unsigned();
...
$table->datetime('appt_date_time')->nullable();
...
$table->timestamps();
$table->index(['patient_id', 'hospital_id', 'appt_date_time']);
$table->foreign('patient_id')
->references('id')->on('patients')
->onDelete('cascade');
});
The Controller
$currPatientCollection = Patient::with('lastappt')
->with('nextappt')
->where('office_id', $user->office_id)
->where('current_patient', true)
->orderBy('last_name')
->orderBy('first_name')
->get();
This returns a nextappt only, even though the database has appts in the past. The following query works (as I 'expected'), though, and returns the patient record with the lastappt and the nextappt.
$currPatientCollection = Patient::with('lastappt')
->with('nextappt')
->where('id', 1)
->where('current_patient', true)
->orderBy('last_name')
->orderBy('first_name')
->get();
Any help would be appreciated and thank you in advance!
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire