CakePHP pagination, how do I sort by the count of a contained model?

Question

Using CakePHP 2.3, I'm retrieving data using a paginator. So, say my models are Countries having many Cities, and in my CountryController I have...

$this->Paginator->settings = [
    'fields' => [
         'id'
         'country_name'
     ],
     'contain' => [
         'City' => [
             'id'
             'city_name',
             'conditions' => [
                 'population >' => 1000000;
             ]
         ]
     ]
];

...which gets me a list of all counties with each row containing a list of any populous cities.

In the view I am obviously able to iterate foreach ($cities as $city) and echo $country['country_name'] etc. and also if I wish I can show a count of the contained cities by echoing count($country['City']).

Using the paginator I can sort the country results by passing back a field name in the query string, e.g. sort=country_name, but how can I get the results to sort by the count of the contained cities?

Answer 1

It is unfortunately not possible to sort by the count of the hasMany Table using the custom Cakephp Pagination. Your best bet is to use counterCache as described in the Docs.

You will need to have a field in country table, named as city_count. This field will be updated in the Country Table automatically by Cakephp whenever there is a save operation on city table.

Since you only want to count the cities with population > 100K. You can specify the condition in counterScope which will only update the column when condition is met.

This can be defined in your City Model as below:

class City extends AppModel {
    public $belongsTo = array(
        'Country' => array(
            'counterCache' => true,
            'counterScope' => array(
              'City.population > ' => 1000000
            )            
        )
    );
}