Cakephp subquery in paginate

I have an existing cakephp (version 2) controller index function doing this:

$options = ['Person.name LIKE' => $term];
$this->set('people', $this->Paginator->paginate($options));

resulting in a paginated table in the view.

My Person model references a child model of Appointment, where one person has many appointments like so:

public $hasMany = [
    'Appointment' => [
        'className' => 'Appointment',
        'foreignKey' => 'person_id',
        'dependent' => false
    ]
]

I now need to add a Person's Oldest Appointment Date column to my table, i.e. if working with raw SQL I might do this:

select 
    Person.id,
    Person.name,
    (select 
        min(Appointment.Date) from Appointment
        where Appointment.person_id = Person.id
    ) as OldestAppointmentDate
from Person
where Person.name like 'foo%'

How can I modify the paginate() parameters so that this new field is included in the results and is sortable by paginate in the usual way?

Solution

The most simple way would probably be to use a virtual field, which you can then include in the paginators fields option, something like:

// in Model/Person.php

public $virtualFields = array(
    'OldestAppointmentDate' => '
        select
            min(Appointment.Date)
        from
            Appointment
        where
            Appointment.person_id = Person.id
    ';
);

// in your controller action

$this->Paginator->settings['fields'] = array(
    'Person.id',
    'Person.name'
    'Person.OldestAppointmentDate'
);

// ...

That will include the subquery and create the required aliases accordingly, and things get stitched together automatically so that the results look like as if OldestAppointmentDate is an actual field of Person, and you can refer to it in the paginator helper like any other field, ie:

$this->Paginator->sort('Person.OldestAppointmentDate');