CakePHP nested associations, empty records errors

Question

I have few tables which are connected (hasMany) like a tree: 1 -> 2 -> 3. There are few records in table 1, table 2 and 3.

I'm using CakePHP to fetch all data from table 1 with connected table 2, which is connected with table 3. However few records in table 1 don't have any connected records in table 2. The same is in table 2, some records don't have connected records in table 3.

For the second situation scripts work fine. I get something like this: 1 -> 2 -> empty. But in the first situation, when data looks similar to: 1-> empty -> empty I get errors that table 3 doesn't exist.

Is there any solution to skip this errors and get pretty formatted association table as return to my query?

$options = array(
        'conditions' => array(
            'Table1.id' => $table1_ids
        ),
        'contain' => array(
            'Table2' => array(
                'conditions' => array(
                    'id' => $table2_ids
                ),
                'Table3' => array(
                    'conditions' => array(
                        'date_end >' => date('Y-m-d H:i:s')
                    ),
                    'fields' => array('id'),
                ),
                'fields' => array()
            ),
        ),
        'fields' => array('id', 'name')
    );
    $this->Table1->recursive = -1;
    $table1 = $this->Table1->find('all', $options);

hard to know without viewing the code that actually executes the script. — Nunser
I've added code. The solution for this could be for each record in Table2 I should retrieve for records in Table3. However it means a lot of queries to database... — VIPPER
What is strange, is that even for some records in Table 1, where Table1 -> empty -> empty, it's still working properly. I can't understand CakePHP. This is good framework for small applications but for more advanced ones it doesn't keep up. — VIPPER
I get database error that Table3 doesn't exist (?!). I don't know why... — VIPPER

user221931 user221931 · Accepted Answer · 2014-09-10T11:13:54

It's not really cakephp to blame here but your lack of understanding what contain does.

What you're looking for are left joins, which is actually hinted in the documentation of contain.

CakePHP nested associations, empty records errors

1 Answers