Doctrine query builder with same field names

Question

I am working with Symfony and Doctrine. I have two entities with Many to One relationship: services and groups. I want to setup a specific query where I get some properties of my service and its group name. Unfortunately, s.name and g.name are conflicting and I get whichever is setup last. How can I get both?

public function findRest($parameters)
{
    // Initiate the query
    $qb = $this->createQueryBuilder('s')
        ->select('s.id')
        ->addSelect('s.mnemonicCode')
        ->addSelect('g.name')
        ->addSelect('s.name')
        ->addSelect('s.definitionShort')
        ->addSelect('s.definitionClient')
        ->addSelect('s.updatedOn')
    ;

    // Add joined tables
    $qb->join('s.group', 'g');

    // Set conditions depending on optional JSON parameters
    if (!empty($parameters->id)) {
        $qb->andWhere('s.id = :id')
            ->setParameter('id', $parameters->id);
    }

    if (!empty($parameters->mnemonicCode)) {
        $qb->andWhere('s.mnemonicCode = :mnemonicCode')
            ->setParameter('mnemonicCode', $parameters->mnemonicCode);
    }

    return $qb
        ->getQuery()
        ->getResult();
}

Here is what is returned is that case:

{
    "id": 1,
    "mnemonicCode": "TPADFI-COMCOL",
    "name": "service name",
    "definitionShort": "the short definition",
    "definitionClient": "This is a long definition",
    "updatedOn": {
        "date": "2016-09-29 15:04:21.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    }
},

As you can see, I miss the group name. If I put ->addSelect('g.name') after ->addSelect('s.name') then only the group name is reported.

Answer 1

You can just alias your names (g.name AS group_name and s.name AS service_name):

$qb = $this->createQueryBuilder('s')
    ->select('s.id')
    ->addSelect('s.mnemonicCode')
    ->addSelect('g.name AS group_name')
    ->addSelect('s.name AS service_name')
    ->addSelect('s.definitionShort')
    ->addSelect('s.definitionClient')
    ->addSelect('s.updatedOn')
;

You will get this in return:

{
    "id": 1,
    "mnemonicCode": "TPADFI-COMCOL",
    "group_name": "group name",
    "service_name": "service name",
    "definitionShort": "the short definition",
    "definitionClient": "This is a long definition",
    "updatedOn": {
        "date": "2016-09-29 15:04:21.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    }
},