The Bigbyte Number The Bigbyte Number - 5 months ago 80
PHP Question

Yii2 where condition with concat

i have having problem with full name autocomplete.
i have one usertable, which contains firstname, lastname columns.through ajax.

In Search action this is my code:

$users = (new Query())
->select('*')
->from($userTable)
->where(['like', 'username', $searchTerm])
->orWhere(['like', 'firstname', $searchTerm])
->orWhere(['like','lastname', $searchTerm])
->andWhere(['<>','id', Yii::$app->user->id])
->andWhere(['status'=>self::STATUS_ACTIVE])
->orderBy('username')
->limit(20)
->all();


My Question is when i am trying search firstname with lastname getting empty records.

Thanks for your support i have resolved by keeping concat

$users = (new Query())
->select('*')
->from($userTable)
->where(['like', 'username', $searchTerm])
->orWhere(['like', "CONCAT(firstname, ' ', lastname)", $searchTerm])
->andWhere(['<>','id', Yii::$app->user->id])
->andWhere(['status'=>self::STATUS_ACTIVE])
->orderBy('username')
->limit(20)
->all();

Answer

Could be you can subsitute the two orWhere for firstname and lasta name with

    ->orWhere(" concat('firstname', ' ' ,  'lastname')  like '%" . $searchTerm . "' ")
Comments