Select() with CONCAT() doesn't return the colum

1

HI community,

This is returning the ID only, no error.

$users = User::find()->select(['id','CONCAT(firstname,SPACE(1),lastname) AS fullname'])->where(['isAdmin' => ($isAdmin ? User::PRIVILEGE_ADMIN : ''), 'status' => User::STATUS_ACTIVE])->all();

I have tried the double quote also, no difference.
A real space instead of SPACE(1) either.
Both columns firstname and lastname are there if I use them alone.

Thank you.

2

Either


$users = User::find()

->select('id, CONCAT(firstname,SPACE(1),lastname) AS fullname')

->where(['isAdmin' => ($isAdmin ? User::PRIVILEGE_ADMIN : ''), 'status' => User::STATUS_ACTIVE])

->all();

or


$users = User::find()

->select(['id', new \yii\db\Expression('CONCAT(firstname,SPACE(1),lastname) AS fullname')])

->where(['isAdmin' => ($isAdmin ? User::PRIVILEGE_ADMIN : ''), 'status' => User::STATUS_ACTIVE])

->all();
3

The first one return an error (look like a bug with inserting `` on field ‘lastname’):

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `user` WHERE (`isAdmin`='') AND (`status`=10)' at line 1
The SQL being executed was: SELECT `id`, CONCAT(firstname, SPACE(1), `lastname)` AS `full1name` FROM `user` WHERE (`isAdmin`='') AND (`status`=10)

The second one return only the ‘id’ without error.
So I tried to force the insert of `` myself like:

$users = User::find()
->select('`id`, CONCAT(`firstname`,SPACE(1),`lastname`) AS `fullname`')
->where(['isAdmin' => ($isAdmin ? User::PRIVILEGE_ADMIN : ''), 'status' => User::STATUS_ACTIVE])
->all();

Same result again, only ‘id’ with no error.
Removing the ‘where()’ returned more result but still only with the ‘id’.
So I tried:

->select('id, firstname, lastname AS fullname')

and I only received the ‘id’ and the ‘firstname’. Keyword ‘AS’ look broken somewhere.
Report as a bug Yii, MariaDB?

4

I have this working in one of my search models (integer columns)
(just added the SPACE(1) for this test)

       $query = ItemSearch::find();
		$query->select(['Item.*', 'CONCAT(VehicleId, SPACE(1), Item.id) as vhid',]);
5

OK, I found out the problem. I had a getFullName() attribute that was doing it’s magic job :wink:
So leaving the select() with ‘id, firstname, lastname’ did the job on the array::map(‘id’, ‘fullname’).

Except for the `` handling on the select() side issue.

Thank you.

1 Like