SQLSTATE[42S22]: Column not found when using containable and fields option in CakePHP 2.x

Question

I am using cakephp containable behavior. I have following query

$this->paginate = array(
             'conditions'=>$criteria,                
            'contain' => array(
                'Contact'=>array("conditions"=>array("is_primary"=>1),'fields'=>array('customer_id','email')),
                'Address'=>array("conditions"=>array("is_primary"=>"yes"),'fields'=>array('customer_id','city'))),
             'limit' =>100,
             'order' => 'Customer.name asc'
        );

        $customers = $this->paginate('Customer');

With associations

var $name = "Customer";
    public $actsAs = array('Containable');
    public $hasMany = array(
        'Contact' => array(
            'className' => 'Contact',
            'foreignKey' => 'customer_id',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'counterCache' => ''
        ),'Address' => array(
            'className' => 'Address',
            'foreignKey' => 'customer_id',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'counterCache' => ''
        )
    );

And this is working fine, but when i use fields attribute

$this->paginate = array(
             'conditions'=>$criteria,
             'fields'=>array("Customer.name","Contact.customer_id","Contact.contact_number","Address.title"),
            'contain' => array(
                'Contact'=>array("conditions"=>array("is_primary"=>1)),
                'Address'=>array("conditions"=>array("is_primary"=>"yes"))),
             'limit' =>100,
             'order' => 'Customer.name asc'
        );

It shows the following SQL error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Contact.customer_id' in 'field list'

Can anyone tell me what's going wrong? Thanks!

Answer 1

From the CakePHP 2.x documentation:

When using fields and contain options - be careful to include all foreign keys that your query directly or indirectly requires.

Try adding Customer.id to your fields array:

$this->paginate = array(
    'conditions'=>$criteria,                
    'fields'=>array(
        "Customer.id", "Customer.name","Contact.customer_id","Contact.contact_number","Address.title","Address.customer_id"),
        'contain' => array(
            'Contact'=>array("conditions"=>array("is_primary"=>1)),
            'Address'=>array("conditions"=>array("is_primary"=>"yes"))),
         'limit' =>100,
         'order' => 'Customer.name asc'
    );

Address.customer_id is also needed for the above to work.