CakePhp Containable too many queries

Question

I'm using CakePHP 2.5.2 and having a bit of trouble searching for data efficiently.

In my application I've 3 tables, teams, players, skills... In teams there are 80 records, players 2400 records, skills 2400 records... I want to calculate the average skill of a team...

 //Team model
 public $actsAs = array('Containable');
 public $hasMany = array('Player');

    //Player model
 public $actsAs = array('Containable');
 public $hasOne = array('Skill');
 public $belongsTo = array('Team');

  //Skill model
 public $actsAs = array('Containable');
 public $belongsTo = array('Player');

My research is:

$team =  $this->Team->find('all', array(
    'contain' => array(
        'Player' => array(
             'Skill'
        )
    ),
));
   $this->set('team', $team);

that gives the expected result:

Array
(
    [0] => Array
        (
            [Team] => Array
                (
                    [id] => 1
                    [name] => my_team_name
                )

            [Player] => Array
                (
                    [0] => Array
                        (
                            [id] => 000000419
                            [name] => Name
                            [surname] => Surname
                            [age] => 21
                            [team_id] => 1
                            [Team_id] => 1
                            [Skill] => Array
                                (
                                    [id] => 20
                                    [player_id] => 000000419
                                    [skill] => 599
                                )

                        ), ecc.....

This structure use at least 1680 queries... that are too much for me...

I've tried an other way, that involve just one query, returns a bad data structure but all the information that i need (also redundant). unfortunately follow this way i can not iterate in View to display what i need.

$player =  $this->Team->Player->find('all', array(
            'contains' => array(
            'Skill',
            ),

that returns

Array
(
    [0] => Array
        (
            [Player] => Array
                (
                    [id] => 000000400
                    [nome] => my_player_name
                    [cognome] => my_player_surname
                    [nation_id] => 380
                    [age] => 29
                    [team_id] => 2
                )


            [Team] => Array
                (
                    [id] => 2
                    [nome] => my_team_name
                )

            [Skill] => Array
                (
                    [id] => 1
                    [player_id] => 000000400
                    [average] => 632
                )

        )

    ecc.

Is there a way to iterate in VIEV to get the average skill of every team? Any other solutions?

Thanks!

have you tried joining the models instead of containing them? With join you can do one (big) query instead of tons of simple queries. Or you rather it be with containable? — Nunser

Kurita Takashi Kurita Takashi · Accepted Answer · 2016-02-13T13:40:56

You can use my plugin to solve this issue if you can upgrade CakePHP to 2.6 or later. The plugin has a high compatibility with ContainableBehavior, but generates better queries. I think that the find operation will execute only 2 queries then. I would be happy if you try it.

https://github.com/chinpei215/cakephp-eager-loader

Usage

1. Enable EagerLoader plugin

// In your model
$actsAs = ['EagerLoader.EagerLoader'];

If you are afraid that loading my plugin breaks something somewhere, you can also enable it on the fly.

// On the fly
$this->Team->Behaviors->load('EagerLoader.EagerLoader');

2. Execute the same find operation

$this->Team->find('all', ['contain' => ['Player' => ['Skill']]]);

3. See the query log

You will see the query log such as the following:

SELECT ... FROM teams AS Team WHERE 1 = 1;
SELECT ... FROM players AS Player LEFT JOIN skills AS Skill ON Player.id = Skill.player_id WHERE Player.id IN ( ... );

CakePhp Containable too many queries

2 Answers