bindModel not working on Paginator conditions [CakePHP]

Question

I would like to know If you can help me with a problem I have had for a while now. First, let me explain. I have 3 tables: Payments, Sales and Customers. Every Sale has its customer_id field (FK) and every Payment has its sale_id. So I want to access the customer name within the Payments view, for doing that I made a bindModel to the Model of Sale, like is shown below:

    $this->Payment->bindModel(
       array(
         'belongsTo'=>array(
             'Sale'=>array(
             'className'  =>  'Sale',
             'foreignKey' => 'sale_id',                
           )          
       )
    ), false
);

and set the recursive parameter to two:

$this->Payment->recursive = 2;

The problem comes when I add a condition to the Paginator, because It doesn´t respect the recursive parameter, It doesn´t make the relations to the table Customers, It only makes It to the table Sales. This is how I declare the paginator:

public $paginate = array(
'limit' => 100,             
);

And this is how I set the conditions of the paginator:

$this->Paginator->settings = $this->paginate;   

    $conditions = array();
    if(!empty($customerName)){                  
        $searchTerm = '%' . $customerName. '%';
        $conditions[] = array( "AND" => array ("Customer.name LIKE" => $searchTerm ) );
    }

$this->Paginator->settings['conditions']['AND'] = $conditions;

And then It shows me the following error:

Database Error

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Customer.name' in 'where clause'

Does anybody know what is going on?, I would appreciate any help guys. Thanks in advance.

Edit:

the Payment Model:

    var $belongsTo = array(
    'Sale' => array(
    'type' => 'LEFT'    
    )
);

the Sale Model

     var $belongsTo = array(
                    'Product' => array(
                    'type' => 'INNER',
                    'fields' => array('Product.id, Product.name, Product.multiple')
                    ),
                    'Customer' => array(
                    'type' => 'INNER',
                    'fields' => array('Customer.name, Customer.rfc'),
                    ) 
                 );

Jhon Charles Jhon Charles · Accepted Answer · 2015-08-25T20:11:33

I found the solution, Here It is, just in case somebody needs It:

I set the paginate like this:

$this->paginate['Payment'] = array(
'contain'=>array(
    'Sale'=>array(
        'Customer'
    )
),
'joins'=>array(
    array(
        'table'=>'customers',
        'alias'=>'Customer',
        'type'=>'INNER',
        'conditions'=>array(
            'Customer.id = Sale.customer_id',                             
        )
    )
)

);

The conditions like this:

$this->paginate['Payment']['conditions']['AND'] = $conditions;

And finally:

$this->Paginator->settings = $this->paginate;

I hope It will be useful. Thanks.

bindModel not working on Paginator conditions [CakePHP]

1 Answers