I need to make a search query using $this->Paginate
in CakePHP 3. Following is the code I am using
$searchCondition = array(
'OR' => array(
'Quotes.quotenum LIKE' => "%" . $this->request->data['Quote']['keyword'] . "%",
'Branches.name LIKE' => '%' . $this->request->data['Quote']['keyword'] . '%',
'Contacts.fname LIKE' => '%' . $this->request->data['Quote']['keyword'] . '%',
'Contacts.lname LIKE' => '%' . $this->request->data['Quote']['keyword'] . '%',
'CONCAT(Contacts.fname, Contacts.lname) LIKE' => '%' . $this->request->data['Quote']['keyword'] . '%',
'Quotes.description LIKE' => '%' . $this->request->data['Quote']['keyword'] . '%'
)
);
$cond = array(
'conditions' => array_merge($searchConditions, $searchCondition, $limo),
'order'= > array('Quotes.quotenum desc'),
'contain' => array('Branches','Contacts')
);
$this->set('articleList', $this->paginate($this->Quotes));
As you can see I merge the condition arrays with each other and then send them to paginate. This worked fine in CakePHP 2.7. However now I get the error
Column not found: 1054 Unknown column 'contacts.lname' in 'where clause'.
The lname
column definitely exits in the database table. Is there something I am doing wrong. If so, could someone tell me the right way to do concat search as I have been trying to do this for quite some time.
Yu have to use query expression but this can't be done in a pagination array.
So Following ndn suggestion here's how I would do
create a custom finder. In your QuotesTable file
public function findByKeyword(Query $query, array $options)
{
$keyword = $options['keyword'];
$query->where(
function ($exp, $q) use($keyword){
$conc = $q->func()->concat([
'Contacts.fname' => 'literal', ù
'Contacts.lname' => 'literal']);
return $exp
->or_([
'Quotes.quotenum LIKE' => "%$keyword%",
'Branches.name LIKE' => "%$keyword%",
'Contacts.fname LIKE' => "%$keyword%",
'Contacts.lname LIKE' => "%$keyword%",
'Quotes.description LIKE' => "%$keyword%"
])
->like($conc, "%$keyword%");
}
);
return $query;
}
Then in your controller
$this->paginate = [
'finder' => [
'byKeyword' => [
'keyword' => $this->request->data['Quote']['keyword']
]],
'conditions' => $limo, // this will merge your $limo conditions
// with the ones you set in the custom finder
'order'= > ['Quotes.quotenum desc'],
'contain' => ['Branches','Contacts']
];
$this->set('articleList', $this->paginate($this->Quotes));