Yii1: How to use concat function in mysq like query

Question

I want to use Mysql Concat Function in like expression.
I want to merge firstname and lastname as fullname and get matched results according to the fullname.
I have tried this in YII1. Below is my code:

    $criteria = new CDbCriteria();
    $criteria->select = "*";
    $criteria->select = 'CONCAT(firstname , "" ,  lastname) AS fullname';
    $criteria->addCondition('fullname LIKE :match');
    $criteria->params = array(':match' => $query);
    $models = User::model()->findAll($criteria);

Below is the generated error message:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'fullname' in 'where clause'
(Error 500)
    CDbCommand::fetchColumn() failed: SQLSTATE[42S22]: Column not found: 1054
Unknown column 'fullname' in 'where clause'. The SQL statement executed
was: SELECT COUNT(*) FROM `members` `t` WHERE fullname LIKE :match.

Thanks in advance

Answer 1

If you don't need the fullname afterwards, you can just use the CONCAT method in the WHERE clause:

$criteria = new CDbCriteria();
$criteria->addCondition('CONCAT(userId , " " , username) LIKE :match');
$criteria->params = array(':match' => $query);
$models = User::model()->findAll($criteria);

However, if you do want to keep the fullname in the SELECT clause, you can only use this alias in the HAVING clause:

$criteria = new CDbCriteria();
$criteria->select = '*, CONCAT(userId , " " , username) AS fullname';
$criteria->having = 'fullname LIKE :match';
$criteria->params = array(':match' => $query);
$models = User::model()->findAll($criteria);

Please note that your User model should have a fullname attribute in this case, otherwise you won't be able to access the fullname field.

Answer 2

I think this is a more OO-fashioned way to use SQL Functions(@JonathanStevens's answer is correct btw):

$criteria = new CDbCriteria();    
    $criteria->select = ['*', new \CDbExpression("CONCAT(firstName, ' ', lastName) as fullname")];
    $criteria->addCondition('CONCAT(firstName, ' ', lastName) LIKE :match'); <<<< This is still a problem
    $criteria->params = array(':match' => $query); 
    $models = User::model()->findAll($criteria);

UPDATE

If you are using Mysql the problem is using this aggregate column(fullname) in where clause.
If you just select that column(fullname) and get it by an artificial property in your model, it is ok but if you want to use it in where clause of a query, it is impossible because it is a restricted by MYSQL server. This is the error you will get:

#1054 - Unknown column 'fullname' in 'where clause'

So you need to repeat your SQL function(concat) in where clause too(you can not use alias columns in where clause).