I'm developing a Plugin with ModelA hasMany ModelB. I can save correctly all data with a simply saveAll across the two models.

But when I try to execute a simple query across the two models something strange happens.

This query work:

$ModelA = new ModelA();
$result = $ModelA->find('all', array(
    'recursive' => 1
));
pr($result);

Array
(
    [0] => Array
        (
            [ModelA] => Array
                (
                    [id] => 1
                    [field] => value
                )
            [ModelB] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [model_a_id] => 1
                        )
                    [1] => Array
                        (
                            [id] => 2
                            [model_a_id] => 1
                        )
                )
        )
)

But adding a condition on ModelB don't work:

$ModelA = new ModelA();
$result = $ModelA->find('all', array(
    'recursive' => 1,
    'conditions' => array(
        'ModelB.id' => 2
    )
));
pr($result);
Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ModelB.id' in 'where clause'

But this work:

$ModelA = new ModelA();
$result = $ModelA->ModelB->find('all', array(
    'recursive' => 1,
    'conditions' => array(
        'ModelA.field' => 'value'
    )
));
pr($result);

Array
(
    [0] => Array
        (
            [ModelB] => Array
                (
                    [id] => 2
                    [model_a_id] => 1
                )
            [ModelA] => Array
                (
                    [id] => 1
                    [field] => value
                )
        )
)

Someone have an idea? Thanks!

Separate queries

CakePHP uses separate queries for hasMany and HABTM associations. The first one retrieves the main model data but doesn't join in associated tables, while the second one uses the IDs from the first queries results to fetch the associated records, which are then merged into the results.

Now the problem is that your conditions will be applied to the first query where the table of the associated model isn't included, hence the error.

The other way around, ie a find on ModelB, it's a belongsTo association, where only one query is being used and the other table is joined into that query, and so the condition applies just fine.

Manual joins as an option

While it's easier in your specific example to simply query ModelB instead, this problem can also be solved by using manually defined joins, as described in the Cookbook:

http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#joining-tables

$ModelA->find('all', array(
    'joins' => array(
        array(
            'table' => 'model_bs',
            'alias' => 'ModelB',
            'type' => 'LEFT',
            'conditions' => array('ModelB.id = ModelA.model_b_id')
        )
    ),
    'conditions' => array(
        'ModelB.id' => 2
    )
));

This way the model_bs table is joined into the first query and you can safely test for conditions on ModelB.

Note that if you wouldn't want to actually retrieve the data of ModelB (which would be an additional query again), you'd need to set recursive to -1.

ps

Never instantiate models like new Model(), use ClassRegistry::init(), Controller::loadModel(), or define the models to load in Controller::$uses.