markvdlaan93 markvdlaan93 - 6 months ago 107
PHP Question

Concat fields in search plugin Cakephp 3

Currently, I'm implementing the friendsofcake search plugin (version 1.2.3). Everything works as it should be with the exception that I want to concat two fields like this:

$this->searchManager()
->add('id', 'Search.Value')
->add('q', 'Search.Like', [
'before' => true,
'after' => true,
'field' => [$this->aliasField('username'), $this->aliasField('name')]
])
->add('q', 'Search.Callback', [
'callback' => function ($query, $args, $manager) {
return $query->select(['name' => $query->func()->concat(
['first_name' => 'identifier', ' ' , 'last_name' => 'identifier']
)]);
}
]);


Since my database table stores first_name and last_name separate I want that the search plugin searches for these two fields combined (the name). The code above ignores the like clausule and when I print the query inside the callback it seems it ignores the like operation. Does anyone know how I can concat two fields within this search plugin?

ndm ndm
Answer

You cannot add multiple filters with the same name (q), the latter will overwrite the former, they won't get merged.

You'll have to build the conditions yourself in the callback too, ie add conditions for username and name. Also you have to use HAVING if you want to match using aggregated columns from the SELECT clause, or you have to build the aggregated column in the WHERE conditions too - depending on the DBMS that you are using, WHERE might be faster (though probably not when using LIKE, which makes using indices impossible, ie every single row will be touched anyways).

Here's a basic example using HAVING:

'callback' => function ($query, $args, $manager) {
    return $query
        ->select([
            $this->aliasField('username'),
            'name' => $query->func()->concat([
                'first_name' => 'identifier',
                ' ' ,
                'last_name' => 'identifier'
            ])
        ])
        ->having([
            'OR' => [
                $this->aliasField('username') . ' LIKE' => '%' . $args['q'] . '%',
                'name LIKE' => '%' . $args['q'] . '%'
            ]
        ]);
}

* untested example

Comments