Laravel 4 sort by related model

Question

My project involves medical appointments using Laravel 4. Each appointment is linked to a doctor, a patient, a status, and one or more insurance cards.

So far this is working great:

$appointments = Appointment::
    with('doctor','patient','status','insurances')->
    orderBy($this->input['sidx'], $this->input['sord'])->
    paginate($this->input['rows']);

The challenge is, sorting only works as long as I am sorting an attribute of the appointment. How would I adjust things if I wanted to sort by doctor.last_name or patient.last_name?

user1669496 · Accepted Answer · 2014-03-07T13:20:35.777

0

It's quite easy, just have to send a key value array of the relationships with the relationship name as the key and the value as a closure.

function cmpDoctor($a, $b)
{
    if ($a->doctor->last_name == $b->doctor->last_name) {
        return 0;
    }
    return (strtolower($a->doctor->last_name) < strtolower($b->doctor->last_name)) ? -1 : 1;
}

$appointments = Appointment::
    with(array('doctor' => function($q)
    {
        $q->orderBy('last_name')->first();
    },'patient' => function($q)
    {
        $q->orderBy('last_name');
    },'status' => function($q)
    {
        $q->orderBy('description');
    },'insurances' => function($)
    {
        $q->orderBy('some_col')
    }))->
    orderBy($this->input['sidx'], $this->input['sord'])->
    paginate($this->input['rows']);

usort($appointments, array($this, 'cmpDoctor'));

For this to make any sense, I'm assuming each appointment would only have one doctor (how would you sort appointments by the doctors if each appointment had more than one doctor?)

I'm using first() on the doctor relationship just in case my assumptions are wrong.

edited Mar 07 '14 at 13:20

answered Mar 06 '14 at 22:19

user1669496

32,176
9
73
65

I think I get what you're saying, but will that sort all of my appointments, or just the models inside the appointment? – Anthony Mar 06 '14 at 22:24
No, it will just sort the collections inside the appointments. The appointments would still get sorted by `orderBy($this->input['sidx'], $this->input['sord'])->` – user1669496 Mar 06 '14 at 22:30
Ok. What I was looking for was the ability to sort the actual appointments by the attributes of a related model. For example, if there are 50 appointments, sort them by the name of their doctor or sort them by the last name of the patient. The appointment itself doesn't have that information - it just points to the patient and the doctor. – Anthony Mar 06 '14 at 22:34
Okay, I've updated the answer to use `usort()` to sort the collection based on the doctors. To make it really easy on yourself, if you are looking to sort by doctor, you can also start at doctors instead of appointments. – user1669496 Mar 07 '14 at 13:22
Thanks for the help. That particular method only sorts the collections within each appointment, however. I was looking to have the appointments sorted by a related model. My answer below is what I was looking for. join() was the key. – Anthony Mar 07 '14 at 16:34

score 0 · Answer 2 · edited May 23 '17 at 11:49

Found the answer at Eloquent eager load Order by. Luis Dalmolin's answer worked perfectly - it is a matter of using JOIN.

Here are the final results:

    $appointments = Appointment::
        with('doctor','patient','status','insurances')->
        join('doctor', 'appointment.doctor_id', '=', 'doctor.id')->
        join('patient', 'appointment.patient_id', '=', 'patient.id')->
        join('status', 'appointment.status_id', '=', 'status.id')->
        orderBy($this->input['sidx'], $this->input['sord'])->
        paginate($this->input['rows']);

Laravel 4 sort by related model

2 Answers2