[FIXED] Find by associated data on paginate

Issue

I have two: Providers and Purchasetopay with the following association:

$Providers->hasMany('Purchasetopay', [
   'foreignKey' => 'providers_id'
]);

and I also have the following action:

public function fornecedores(){

        $Providers = TableRegistry::get('Providers');

        $pesquisar = $this->request->query('pesquisar');

        if(!empty($pesquisar)){
            $this->paginate = [
                'conditions' => 
                            [ 'OR' => 
                                ['Providers.cnpj' => $pesquisar,
                                 'Providers.corporatename LIKE' => '%'.$pesquisar.'%',
                                ]                              
                            ]
                         ];
        }

        $query = $Providers->find()->contain(['Telephones','Purchasetopay'=>['SupplierNotes']]);
        $providers = $this->paginate($query);
        $this->set(compact('providers'));

    }

I need to add Purchasetopay field to OR conditions. How? I saw this solution: https://stackoverflow.com/a/39559315/11239369 However, I don't know how to adapt my code to work this way.

Solution

You can use orWhere/andWhere as below :

$query = $Providers->find()
          ->contain(['Telephones','Purchasetopay'=>['SupplierNotes']])
          ->where( ['Providers.cnpj' => $pesquisar])
          ->orWhere(['Providers.corporatename LIKE' => '%'.$pesquisar.'%' ]);

Answered By - Shifat