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, Jul 04 '14 at 13:42

Kurita Takashi · Answer 1 · 2016-02-13T14:24:41.190

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 ( ... );

Hi and welcome to SO. To make this answer more useful you should explain a bit about how to use the library or at least point to instructions relevant to the specific question. — Rory, Feb 13 '16 at 13:49

score -2 · Answer 2 · answered Jul 04 '14 at 12:32

-2

if you feeling that query searching so many tables (ie, models) then you can unbind those model, before performing search with find()
if you want to fetch some particular column of a table, then remove others column by selecting "fields" in find().

answered Jul 04 '14 at 12:32

Arka

581
5
18

1

That won't help, just look at the associations, all of them are required. As mentioned by @Nunser the proper solution is using joins (and MySQL functions like `AVG` for the calculation). – ndm Jul 04 '14 at 13:56

CakePhp Containable too many queries

2 Answers2