Sort the parent model based on the child model / relationship

user_10384750 0
Viewed 332

I have a model called appointments, each appointment has an option_id that is linked through a one to one relationship and the option_id can also be null. The option model has a property datetime_start. I want to sort the appointments based on the option.datetime_start.

Here is my code :

$appointments = $user->appointments()
->with(['option'
])
->get();

Edit :

Appointment model :

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Appointment extends Model
{
   /**
         * @return \Illuminate\Database\Eloquent\Relations\HasOne
   */
   public function option()
   {
     return $this->hasOne(Option::class, 'id', 'option_id');
   }
}

Option model :

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Option extends Model
{
    protected     $fillable = ["appointment_id",
                               "datetime_start"
    ];

    public function appointment()
    {
        return $this->belongsTo(Appointment::class, 'id','appointment_id');
    }
}

Any help would be greatly appreciated.

eloquent laravel model orm mysql
2 Answers

In order to sort Appointment model by a column from related Option model, you need to add a JOIN to your query so that you can sort by related fields:

$appointments = $user->appointments()
  ->leftJoin('options', 'options.appointment_id', '=', 'appointments.id')
  ->orderBy('options.datetime_start', 'ASC')
  ->with(['option'])
  ->get();

This will give you all appointments, including those without Option - the ones without an Option and, hence, without datetime_start, will be all returned either at the beginning or the end of the result list, you'll need to check that. If you only want appointments with Option, replace leftJoin() with join().

user_15949150

$appointments = Appointment::with(['option' => function ($query){ $query->orderBy('datetime_start', 'desc'); } ])->get();

user_42238570
Related