CakePHP 2.x - Virtual fields max foreach items

Question

System

• CakePHP 2.x

Database

Houses --> id, name
Visits --> id, date, house_id

Models

Visit belongsTo House
House hasMany Visit

View

• I have a view for all houses called houses/index.ctp.
• It lists each house with their id and their name.
• I also use the PaginatorHelper to sort my array.

Issue

• I'd like to see for each house last visit they had.
• This have to be sortable by the PaginatorHelper

Tries

• I thought that I could do it with virtualFields but I failed (it always returns the max value of all houses).

  public $virtualFields = array('lastvisit' => 'SELECT MAX(visits.date) FROM visits, houses WHERE houses.id = visits.house_id GROUP BY house_id');

• I tried to "cache" each max visit in a new column of Houses table but it's very dirty.

Thank you for helping.

Answer 1

Declaring a virtual field for this may be tricky, so I suggest you to add it on the fly whenever you need it.

So your HousesController's index action will look like this:

public function index() {
    $this->House->virtualFields['last_visit'] = 'MAX(Visit.date)';
    $this->paginate = array(
        'House' => array(
            'group' => array('house_id'),
            'joins' => array(
                array(
                    'table' => 'visits',
                    'alias' => 'Visit',
                    'type' => 'INNER',
                    'conditions' => array(
                        'House.id = Visit.house_id',
                    )
                )
            )
        )
    );

    $this->set('houses', $this->paginate());
}

Notice you have to remove the $publicFields declaration from your model, and that I changed the name of the field to from lastvisit to last_visit to make it more Cake.

Hope it helps.