CODEWITHSUNDEEP
phpmysqlactiverecordyiiyii1.x
FahadAkram
FahadAkram

Reputation: 465

Yii1: How to use concat function in mysq like query

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

Upvotes: 2

Views: 2510

Answers (2)

Mohamad
Mohamad

Reputation: 1065

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).

Upvotes: 1

JonathanStevens
JonathanStevens

Reputation: 472

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.

Upvotes: 2

Related Questions