join belongsTo in find method

Question

Score belongs to Player:

class Score extends AppModel {
    public $name = 'Answer';
    public $belongsTo = array('Player');
}

In PlayersController, I want to get player scores, with his details.

Question #1: How to include belongsTo model in find method result? (join it)

Question #2: How to get sum of all scores distance (Score.distance) which belongs to that player? (I mean SUM(Score.distance), group by Score.player_id)

Note for Q1: Because each player has a lot scores, I don't like to join scores in each find method I use in that controller. I want to get them in just 1 action)

Answer 1

Cakephp automagic not actually join tables as you can see in sql_dump in your layout at bottom.

You will see below queries.

SELECT `Player`.`id`, `Player`.`name`, `Player`.`created` FROM `players` AS `Player` WHERE `Player`.`id` = 10 GROUP BY `Player`.`id`

SELECT `Score`.`id`, `Score`.`player_id`, `Score`.`scores` FROM `scores` AS `Score` WHERE `Score`.`player_id` = (10) 

Quite clear that its not actually join table so you need to do it following way.

And following will help you because i have already tested it.

In Player Controller you need to unbind score model first and then custom code to join score table as below.

In player model create one virtual field as below.

<?php
class Player extends AppModel
{
    var $name = 'Player';
    var $displayField = 'name';

    var $virtualFields = array
    (
        'Distance' => 'SUM(Score.scores)'
    );

    var $hasMany = array
    (
        'Score' => array
        (
            'className' => 'Score',
            'foreignKey' => 'player_id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        )
    );
}
?>

For testing i manually added conditions to find player number 10 scores

<?php
class PlayersController extends AppController {

    var $name = 'Players';

    function index()
    {

        $this->Player->unbindModel(array
        (
            'hasMany' => array
            (
                'Score'
            )
        ));

        $this->Player->bindModel(array
        (
            'belongsTo' => array
            (
                'Score' => array
                (
                    'foreignKey' => false,
                    'conditions' => array
                    (
                        'Player.id = Score.player_id'
                    )
                )
            )
        ));

        $order = "Player.id";
                $direction = "asc";
                if(isset($this->passedArgs['sort']) && $this->passedArgs['sort']=="Distance")
                {
                        $order = $this->passedArgs['sort'];
                    $direction = $this->passedArgs['direction'];
                    unset($this->passedArgs['sort']);
                    unset($this->passedArgs['direction']);
                }

                $this->pagination = array();

                $this->pagination = array
                (
                    'conditions' => array
                    (
                     'Score.player_id' => 10
                    ),
                    'fields' => array
                    (
                        'Player.*',
                        'SUM(Score.scores) AS Distance'
                    ),
                    'group' => array
                    (
                        'Score.player_id'
                    ),
                    'limit' => 15,
                        'order'=>$order." ".$direction
               );

              $playersScore = $this->paginate('Player');
    }   
}
?>

And the resulting array would be looks like below.

Array
(
    [0] => Array
    (
        [Player] => Array
        (
            [id] => 10
            [name] => Dexter Velasquez
            [created] => 2012-08-02 12:03:07
            [Distance] => 18
        )
    )
)

For testing i have used Generate Mysql Data.

for sorting link

<?php $direction = (isset($this->passedArgs['direction']) && isset($this->passedArgs['sort']) && $this->passedArgs['sort'] == "Distance" && $this->passedArgs['direction'] == "desc")?"asc":"desc";?>

and display above link as below.

<?php echo $this->Paginator->sort('Distance','Distance',array('direction'=>$direction));?>