Doctrine DQL - Select column alias added as new record

Question

I have a user entity linked to a user profile entity.

I need to be able to concatenate a users first name and last name for filtering purposes.

This is roughly how my query looks like :

$qb = $this->createQueryBuilder('users');
$qb
    ->addSelect('users')
    ->innerJoin('users.profile', 'profile')
    ->addSelect('profile')
    ->addSelect('CONCAT(profile.firstName, \' \', profile.lastName) AS fullName')
;

I would expect the fullname to be treated as a user property, or profile, but instead i get the fullName as a whole new record in the results:

[0] => Array (
    [id] => 5
    [username] => admin
    [profile] => Array (
        [firstName] => John
        [lastName] => Doe
    )
), 
[fullName] => John Doe

I tried aliasing the column as "users.fullName" but that gives me an error.

[Syntax Error] line 0, col 87: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got '.'

What is the proper way to do this ?

Thanks

Answer 1

Why don't you provide the whole list of needed columns in select method:

    ->select("user.id, user.username, CONCAT(profile.firstName, ' ', profile.lastName) AS fullName")

And you should receive proper assoc array.

Remember to add proper where clause:

    ->where("CONCAT(profile.firstName, ' ', profile.lastName) LIKE :name")
    ->setParameter('name', '%' . $name . '%')