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

Do you want to match the fullName in the query or later in PHP? — lxg
Use native query instead then map the field to the class properties using the ResultSetMapping. — emix

Kamil Adryjanek Kamil Adryjanek · Accepted Answer · 2018-08-10T08:44:25

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

Doctrine DQL - Select column alias added as new record

1 Answers