Symfony/Doctrine relation COUNT in entity

Question

As the title states,

I would like to run 1 query to get results from a table with the count of their respective relationships.

Lets say I have a Person entity with a OneToMany relationship with a Friend entity

The Person entity might look something like the following:

class Person
{
    /**
     * @ORM\OneToMany(...)
     */
    protected $friends;

    public function __construct()
    {
        $this->friends = new ArrayCollection();
    }

    ...
}

A classic SQL solution to what I want to achieve might look like the following:

SELECT p.*, COUNT(f.id) as friendsCount
FROM Persons p
LEFT JOIN Friends f
ON f.person_id = p.id
GROUP BY p.id

Now I am wondering if this could be done in DQL as well and storing the count value into the Person Entity

Lets say I expand the Person entity like: (Keep in mind this is just an idea)

class Person
{
    /**
     * @ORM\OneToMany(...)
     */
    protected $friends;

    protected $friendsCount;

    public method __construct()
    {
        $this->friends = new ArrayCollection();
    }

    ...

    public function getFriendsCount()
    {
        return $this->friendsCount;
    }
}

Now I am having trouble finding how I could populate the count value in the entity from DQL:

SELECT p, /* What comes here ? */
FROM AppBundle\Entity\Person p
LEFT JOIN p.friends f
GROUP BY p.id

PS: I do know I could just do:

$person->getFriends()->count();

And even mark it as extra lazy to get the count result.

I just though this count relationships example demonstrated well what I am trying to do.

(Which is populating the entity's non @ORM\Column properties from dql)

Is this possible with Doctrine ?

Is this breaking some solid principles ? (SRP ?)

Cookie for your thoughs ;)

Answer 1

You probably just want to select the count as you need it, as described above with $person->getFriends()->count();. However, you can select both an object and a count at the same time (see these Doctrine query examples), there is one very similar to what you are doing:

SELECT p, COUNT(p.friends)
FROM AppBundle\Entity\Person p
LEFT JOIN p.friends f
GROUP BY p.id

What should be returned back is an array of arrays with the object and count, like so:

[
    [Person, int],
    [Person, int],
    ...,
]

Your best bet would be to make that a Repository call on your PersonRepository, something like findPersonsWithFriendCount(), which would look like:

public function findPersonsWithFriendCount()
{
    $persons = array();

    $query = $this->_em->createQuery('
        SELECT p, COUNT(p.friends)
        FROM AppBundle\Entity\Person p
        LEFT JOIN p.friends f
        GROUP BY p.id
    ');

    $results = $query->getResult();

    foreach ($results as $result) {
        $person = $result[0];
        $person->setFriendsCount($result[1]);

        $persons[] = $person;
    }

    return $persons;
}

Keep in mind you'd need to add a setFriendsCount() function on your Person object. You could also write a native SQL query and use result set mapping to automatically map your raw result's columns to your entity fields.