Using Containable behavior to filter results in Cakephp 2.2.4

Question

I'm using containable behavior and the result of my find('all') is:

array(
    (int) 0 => array(
        'User' => array(
            'id' => '106',
            'email' => '[email protected]',
            'pwd' => '0433c024cb08be13000d59a347e640482843f46f177e95749dc6599c259617fd3491dcb940b47693cbbc7f65a2cc5ef62deca2e600c1be133ad54170f7d1fbd1',
            'role_id' => '3',
            'active' => '1'
        ),
        'Lead' => array(
            'id' => '6'
        ),
        'Estimate' => array(
            (int) 0 => array(
                'lead_id' => '6',
                'Estimate' => array(
                    (int) 0 => array(
                        'TOT_count' => '2'
                    )
                )
            )
        )
    )
)

I need to to count how many estimates there are in the lead.

The total (2) is correct, but i see nested 'Estimated' array, why ?

The result i would like to get is:

array(
    (int) 0 => array(
        'User' => array(
            'id' => '106',
            'email' => '[email protected]',
            'pwd' => '0433c024cb08be13000d59a347e640482843f46f177e95749dc6599c259617fd3491dcb940b47693cbbc7f65a2cc5ef62deca2e600c1be133ad54170f7d1fbd1',
            'role_id' => '3',
            'active' => '1'
        ),
        'Lead' => array(
            'id' => '6'
        ),
        'Estimate' => array(
          'TOT_count' => '2'
        )
    )
)

This is the find:

$options = array(
                    'contain' => array(
                        'User',
                        'Estimate' => array(
                            'fields' => 'COUNT(*) AS TOT_count'                             
                        )   
                    ),
                    'conditions' => array('Lead.id' => 6),
                    'fields' => 'User.*',                           
                    'limit' => 1

                );

    debug($this->Lead->find('all', $options));

How can i do it? Thanks!

Oldskool Oldskool · Accepted Answer · 2013-01-09T20:58:54

When you use a "custom" AS statement, in your case TOT_count, Cake will always put this in a result key called 0. You can avoid this by defining TOT_count as a virtualField in your model. That way it will be nested directly under the model name in your resultset.

Secondly, the lead_id is forcedly retrieved, because it is "needed" to make the join with the Lead model. It can not properly retrieve all the data without that piece of information there.

Using Containable behavior to filter results in Cakephp 2.2.4

1 Answers