Cakephp Pagination Sort data out of model

I currently have a query that results in the following recordset:

Array ( [Contestant] => Array ( [id] => 1 [name] => test [age] => [city] => atest [telephone] => [email] => test@test.com [why_model_house] => a [highschool] => [photo] => 5开发者_如何转开发329_119145013633_512383633_2487923_7196193_n0.jpg [active] => 1 ) [0] => Array ( [Contestant_votes] => 4 ) [Vote] => Array ( [id] => 1 ) )

I can get the paginator->sort to work with every data in it except the "Contestant_votes" since it doesn't belong in a model it's currently in the Array[0]

I tried doing this :

        <th><?php echo $paginator->sort('Votes', '0.Contestant_votes'); ?></th> 

and this:

        <th><?php echo $paginator->sort('Votes', 'Contestant_votes'); ?></th> 

But it doesn't work. The conestants_votes field is generated by the following query:

'Contestant.*, count(Vote.contestant_id) as Contestant_votes'

So that's why it's not in a model. Is there a way to trick cakephp into thinking that Contestant_votes is part of the Contestant model or a way to add it to the paginator so I can sort it?

Thanks in advance,

Fabian Brenes

This is exactly what CakePHP 1.3 virtual fields are meant for.

In your case, put following in your Vote model:

var $virtualFields = array(
    'Contestant_votes' => 'COUNT(Vote.contestant_id)'
);

Beware though - behaviour of this virtual field will change if you add grouping to your query.

Change your Contestant_votes column in your query to include 2 underscores (i.e.: Contestant__votes). The Cake automagic will then include it as part of your Contestant array data, as follows:

Array (
    [Contestant] => Array (
        [id] => 1
        [name] => test
        [age] =>
        [city] => atest
        [telephone] => 
        [email] => test@test.com
        [why_model_house] => a
        [highschool] =>
        [photo] => 5329_119145013633_512383633_2487923_7196193_n0.jpg
        [active] => 1
        [votes] => 4
    )
    [Vote] => Array (
         [id] => 1
    )
)

2 underscores do not work for all models?

 var $hasOne = array(

'Rating' => array( 'className' => 'Rating', 'fields' => array('SUM(Rating.score) AS Location__sum','AVG(Rating.score) AS Location__avg','COUNT(Rating.score) AS Location__count'), 'group' => 'Rating.location_id', ) );

cannot generate a resultset having Location['sum']

I took me the better part of three hours. But I finally cracked it.

The double underscore pattern comes from here. It's an extension of the mysql database driver that lets you do those to add any calculated value as a field.

Furthermore I had to hack out the field checking in cake's base controller. Find the code block around line 1000 that starts with:

if (!empty($options['order']) && is_array($options['order'])) {
    // lots of code goes here
}

And replace the entire block (i cut it for brevity) it with this:

if (!empty($options['order']) && is_array($options['order'])) {
 $key = key($options['order']);
 $value = $options['order'][$key];

 $key = preg_replace('/[^a-zA-Z_.]/', '', $key);

 $options['order'] = array();
 $options['order'][$key] = $value;
}

This field validation is VERY naive, and basically just applies a whitelist to avoid any simple sql injections, but your users will be able to mess up the query by changing the url themselves. I'm just using this for an internal backend so it'll have to do for now.

I also stumbled over this link, it would seem there will be a less hacky way to do this in cake 1.3 (the code above was written for 1.2.5)