Laravel 5.6 - Problems using Eloquent to return most recent date in datetime field of related table

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!

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2ObSJOQ
via IFTTT