CakePHP 4 search on virtual column

Question

We have a CakePHP 4.x application which contains a table of users. The database is MariaDB 10.7.3

Users have a first name (fname) and last name (lname). These are represented in the users table as 2 separate columns.

Because we want to output a user's full name in various parts of the application we return a full_name in our query which concatenates fname and lname with a space in between. The code looks like this:

// UsersController::index()
$usersQuery = $this->Users->find();

 $usersQuery->select([
    'full_name' => $usersQuery->func()->concat(['fname' => 'identifier', ' ', 'lname' => 'identifier']),
])
// ...

This works in terms of returning users such that we get full_name in the results set. We can output this data into templates.

But, we can't seem to search on that virtual column. We have the following as part of the query to perform a search:

$usersQuery->where([
    'OR' => [
        'fname LIKE' => "%$searchTerm%",
        'lname LIKE' => "%$searchTerm%",
     ]
]);

This works on fname and lname independently. If the user was called "John Smith" then searching for "John" would return a result. Searching for "Smith" would also return a result. But searching for the full name, "John Smith", doesn't return anything.

I tried adding:

'full_name LIKE' => "%$searchTerm%",

This errors with

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'full_name' in 'where clause'

Can I query on full_name and if not, how can I query a user's fullname based on a 2 column data structure as described in the first paragraph?

I did see some posts about this but they are for older versions of CakePHP and did not help with this.

Answer 1

You cannot refer to aliases in the WHERE clause, at the time of processing that clause, they are not available, that's a limitation of the DBMS.

Either repeat the function call in your conditions:

$usersQuery->where(function (
    \Cake\Database\Expression\QueryExpression $exp,
    \Cake\ORM\Query $query
) use ($searchTerm) {
    return $exp->or([
        'fname LIKE' => "%$searchTerm%",
        'lname LIKE' => "%$searchTerm%",
        $exp->like(
            $query->func()->concat([
                'fname' => 'identifier',
                ' ',
                'lname' => 'identifier'
            ]),
            "%$searchTerm%"
        ),
    ]);
});

or, in case applicable, use having() (make sure that you understand the implications of moving conditions into the HAVING clause before doing so, and keep in mind that the ability to refer to fields in the HAVING clause that are neither in the GROUP BY list, nor are aggregates, is a MySQL/MariaDB specific thing!):

$usersQuery->having([
    'OR' => [
        'fname LIKE' => "%$searchTerm%",
        'lname LIKE' => "%$searchTerm%",
        'full_name LIKE' => "%$searchTerm%",
    ],
]);

See also

• Cookbook > Database Access & ORM > Query Builder > Advanced Conditions