Query Builder how to CONCAT columns and then use LIKE

Need Help
1

For a search field, I want to concat the first and last name and then check if those two together are like the input string.

Basically, I want this:

WHERE CONCAT(client.first_name, '  ', client.last_name) LIKE %inputString%

I have tried something like:

$concat = $query->func()->concat(['client.first_name' => 'identifier', ' ', 'client.last_name' => 'identifier']);

And then in the where I don’t know how to place it as the CONCAT returns a function expression so I can’t simply do [$concat . ' LIKE' => '%inputString%]. I tried the like() function but wasn’t successful either:

->where('OR' => [$concat->like($concat => '%inputString%')]) // Illegal array key type

Can someone point me to the place where this is documented in here?
If it is not there, I really feel that such things should be present in the documentation. I found answers to this question for the laravel query builder but not cakephp so I’d be very grateful for some help.

Note, I’m only using the database module from the cake framework (not the ORM).

And my preferred solution would be without the need for the $query instance at all like

$whereArray = ['OR' => ['CONCAT(first_name, " ", last_name) LIKE' => '%inputString%']];

But I highly doubt that this is possible.

2 
$query = $this->Customers->find();
$resultArray = $query->where(function(QueryExpression $exp, Query $query) {
    return $exp->like(
        $query->func()->concat(
            [$query->identifier('first_name'), ' ', $query->identifier('last_name')],
            ['string', 'string', 'string']
        ),
    '%Your Search String%',
    'string');
})->toArray();

What you are misunderstand here is the difference between strings and identifiers
You have to tell CakePHP, that 'client.first_name' is not a string but an identifier for that table you are querying.

See php - How can I use CONCAT in JOIN conditions with CakePHP? - Stack Overflow
and what NDM linked at the bottom of his answer as well.

3

Thank you for the answer, Kevin.
Is there a way to use this concat outside the ->where() function ?
I’m building the where array outside the repository class that makes the request.
toArray() does not work for me: Method 'toArray' not found in \Cake\Database\Query .
If I do

$whereArray[]['OR'] = [
    ['first_name LIKE' => '%string%'], 
    ['last_name LIKE' => '%string%'], 
    function (QueryExpression $exp, Query $query) use ($filterParams) {
        return $exp->like(
            $query->func()->concat(
                [$query->identifier('first_name'), ' ', $query->identifier('last_name')],
                ['string', 'string', 'string']
            ),
            '%string%',
            'string'
        ) 
    };
];

// Later somewhere else
$query->select($fields)->from('table')->where($whereArray);

I get the error

App\Domain\Client\Service\ClientFilterWhereConditionBuilder::App\Domain\Client\Service\{closure}(): 
Argument #2 ($query) must be of type Cake\Database\Query, 
Cake\Database\Expression\QueryExpression given, called in 
\vendor\cakephp\database\Expression\QueryExpression.php on line 719
4

This should work

$query = $this->MyModel->find();
$exp = $query->expr()->like(
            $query->func()->concat(
                [$query->identifier('first_name'), ' ', $query->identifier('last_name')],
                ['string', 'string', 'string']
            ),
            '%string%',
            'string'
        );

$whereArray[]['OR'] = [
    ['first_name LIKE' => '%string%'], 
    ['last_name LIKE' => '%string%'], 
    $exp,
];

so basically instead of the function wrapper you only need the expression object directly which you can get from $query->expr()

1 Like
5

It does indeed! :tada:

I just had to change the $query->expr() to $query->newExpr() is that because I only use the Query Builder? But anyway, I’m happy. Thank you so much for the help!

6

->newExpr() and ->expr() should do the same thing as you see here…

But I am happy that you have now a working version :smiley: