CakePHP repeats same queries

Question

I have a model structure: Category hasMany Product hasMany Stockitem belongsTo Warehouse, Manufacturer.

I fetch data with this code, using containable to be able to filter deeper in the associated models:

$this->Category->find('all', array(
        'conditions' => array('Category.id' => $category_id),
        'contain' => array(
            'Product' => array(
                'Stockitem' => array(
                    'conditions' => array('Stockitem.warehouse_id' => $warehouse_id),
                    'Warehouse',
                    'Manufacturer',
                )
            )
        ),
        )
    );

Data structure is returned just fine, however, I get multiple repeating queries like, sometimes hundreds of such queries in a row, based on dataset.

SELECT `Warehouse`.`id`, `Warehouse`.`title` FROM `beta_warehouses` AS `Warehouse` WHERE `Warehouse`.`id` = 2

Basically, when building data structure Cake is fetching data from mysql over and over again, for each row. We have datasets of several thousand rows, and I have a feeling that it's going to impact performance. Is it possible to make it cache results and not repeat same queries?

Answer 1

Try this:

$this->Product->find('all', array(
    'conditions' => array('Category.id' => $category_id, 'Stockitem.warehouse_id' => $warehouse_id),
    'contain' => array(
        'Category'
        , 'Stockitem' => array(
            'Warehouse'
            , 'Manufacturer'
        )
    ),
));

If you remove Warehouse and Manufacturer you will find that Cakephp only executes one query.

Alternatively you can create database views for complex queries.

Answer 2

Maybe this article will be helpful for you http://teknoid.wordpress.com/2008/07/17/forcing-an-sql-join-in-cakephp/