Cakephp Pagination Sort data out of model

Question

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

Array ( [Contestant] => Array ( [id] => 1 [name] => test [age] => [city] => atest [telephone] => [email] => [email protected] [why_model_house] => a [highschool] => [photo] => 5329_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

Answer 1

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] => [email protected]
        [why_model_house] => a
        [highschool] =>
        [photo] => 5329_119145013633_512383633_2487923_7196193_n0.jpg
        [active] => 1
        [votes] => 4
    )
    [Vote] => Array (
         [id] => 1
    )
)

Answer 2

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']

Answer 3

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)