Cakephp 3 search query, with concat

Question

I'm trying to perform a search on two joined columns, fname and lname.

This does not appear to be working:

Object of class Cake\Database\Expression\FunctionExpression could not be converted to string

$users = $this->Users->find();
$users->select(['id', 'fname', 'lname'])
    ->where([$users->func()->concat(['fname', 'lname']).' LIKE' => $terms]);

Any ideas?

Thanks.

score 5 · Accepted Answer · answered Sep 27 '16 at 12:49

5

If you're concatenating first and last names, they should be concatenated with spaces in between.

$users->select(['id', 'fname', 'lname'])
      ->where(function ($exp, $query) use ($terms) {
         $conc = $query->func()->concat([
            'fname' => 'identifier', ' ',
            'lname' => 'identifier'
         ]);
         return $exp->like($conc, $terms);
       });

answered Sep 27 '16 at 12:49

Indrasis Datta

8,692
2
14
32

3

This works. Thanks. I also changed the second like parameter to be (in case someone else has the same problem): like($conc, '%'.$terms.'%'); – toast Sep 27 '16 at 23:32

arilia · Answer 2 · 2016-09-27T13:25:19.480

0

I think you have to use SQL expressions. Try something like this:

->where(function ($exp, $q) use($terms) {
    $concat = $q->func()->concat([
        'fname' => 'identifier', 
        'lname' => 'identifier'
    ]);
    return $exp->like($concat, $terms);
});

edited Sep 27 '16 at 13:25

answered Sep 27 '16 at 12:29

arilia

9,373
2
20
44

score 0 · Answer 3 · answered Sep 27 '16 at 13:10

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 UsersTable file

public function findByKeyword(Query $query, array $options)
{
    $keyword = $options['keyword'];
    $query->where(
        function ($exp, $q) use($keyword){
            $conc = $q->func()->concat([
                'Users.fname' => 'literal', ù
                'Users.lname' => 'literal']);
            return $exp
                ->or_([
                    'Users.fname LIKE' => "%$keyword%",
                    'Users.lname LIKE' => "%$keyword%",
                ])
                ->like($conc, "%$keyword%");
            }
        );
    return $query;
}

Controller

$this->paginate = [
        'finder' => [
            'byKeyword' => [
                'keyword' => $this->request->data['Users']['keyword']
        ]],
        'conditions' => $limo,  // this will merge your $limo conditions                  
                                // with the ones you set in the custom finder
        'order'= > ['Users.fname desc'],
    ];

$this->set('userlist', $this->paginate($this->Users));

Cakephp 3 search query, with concat

3 Answers3