Doctrine Symfony getting multiple object columns and multiple columns from a relationship object

Question

friends! I want to get the selected columns of my object from the database, as well as the selected columns of the related objects. But when I execute my query, I get the data but the related objects are not converted into separate arrays but are returned in the general array of the main requested object.

My query:

$query = $this->createQueryBuilder('i')
        ->select('i.id', 'i.payStatus', 'contract.id as contract_id', 'contract.type as type')
        ->join('i.contract', 'contract');
    return $query->getQuery()->getResult();

Current result:

{
  "id": 56,
  "payStatus": 2,
  "contract_id": 5,
  "type": 2
}

Expected Result:

{
  "id": 56,
  "payStatus": 2,
  "contract": {
      "contract_id": 5,
      "type": 2
  }
}

I would be grateful for any help!

Answer 1

When you select multiple scalar fields, doctrine always returns flat array with all the "columns".

There are two options I see:

• you can save $query->getQuery()->getResult() in your query method, map it yourself and return the mapped results.
• if you want to map it to some entities, you can use "native queries" and "result set mapping", then you have can have nested objects in the result while having full control over the mapping. https://www.doctrine-project.org/projects/doctrine-orm/en/2.8/reference/native-sql.html However in the idiomatic doctrine way, you should avoid native queries when possible.