phpcakephpcakephp-3.0query-builder

Search with concat fields in cakephp 3


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.


Solution

  • 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));