在Doctrine中进行多次选择时获取平面数组

Question

I have a query that fetches some information from a table AND the count of related objects from another table. I have trouble expressing it in DQL, so it will return a flat array.

This example fetches the school, the name of the school and the number of students of each school:

$qB = $this->createQueryBuilder('school')
$qB->leftJoin('school.students', 'students');
$qB->select([
    'partial school.{id, name}',
    'count(students) AS number_students',
]);
$qB->groupBy('school');
$qB->getQuery()->getResult();

I am expecting it to return:

[
    [id => 1, 'name' => 'School A', 'number_students' => 5],
    [id => 2, 'name' => 'School B', 'number_students' => 3],
]

But I end up getting this:

[
    [0 => [id => 1, 'name' => 'School A'], 'number_students' => 5],
    [0 => [id => 2, 'name' => 'School B'], 'number_students' => 3],
]

As a workaround it is possible to use $qB->getQuery()->getScalarResult(), but that then converts the variable names (e.g. The entity variable name schoolName is then converted to the column title in the database school_name), which means I would have to remap the fields again.

It is perhaps related in the way Doctrine treats entities. Here is the school entity of the example:

class School {
    protected $id;
    protected $name;
    protected $students;
}

Answer 1

From the DQL above seems like you end up with mixed results and that's Doctrine default behavior when using $qB->getQuery()->getResult().

SELECT u, p.quantity FROM Users u...

Here, the result will again be an array of arrays, with each element being an array made up of a User object and the scalar value p.quantity.

I would try to debug the SQL generated and if I am right then there is your why. IMHO this behavior can not be changed but maybe I am wrong.

In this case the solution could be write a protected PHP function for flatten the array as you want to.

Update:

I haven't tried but seems to be possible to use Doctrine Events to achieve this. There is not built-in Events for preSelect or postSelect however you can create your own Event and register it properly so you can call it at any point. As said before I didn't tried and I am not sure at all that this is possible using any Event (I might be wrong).

Just a side note, I would not try to over complicate this to much, you are adding a layer of complexity to the entity when you can do this easily on your controller by creating a private|public function that you can reuse at any point.

More info:

• DQL Select Results