Populate count of associated entities of multiple entities.

[whataboutpereira]

Hello!

I've a bunch of entities that have extra lazy associations. I'm looking for the most efficient way to load the associated entity count.

    #[ORM\OneToMany(targetEntity: Item::class, mappedBy: 'owner', fetch: 'EXTRA_LAZY')]
    private Collection $items;

    public function getItems(): Collection
    {
        return $this->items;
    }

Right now I've resorted to having a separate query in the actual item repository.

    public function countItemsByOwner(array $owners): array
    {
        $result = $this->createQueryBuilder('i')
            ->select(
                'IDENTITY(i.owner) AS owner',
                'COUNT(i.id) AS items',
            )
            ->where('i.owner IN (:owners)')
            ->setParameter('owners', $owners)
            ->groupBy('i.owner')
            ->getQuery()
            ->getResult()
        ;

        return array_column(
            $result,
            'items',
            'owner'
        );
    }

Is it possible to somehow prime the items counts when getting the owners in the owner repository via query builder?

Right now it seems possibly to only get the whole data for items, but ideally would only need to get the count.

    public function findAll(): array
    {
        return $this->createQueryBuilder('owner')
            ->addSelect('items')
            ->leftJoin('owner.items', 'items')
            ->getQuery()
            ->getResult()
        ;
    }

Later would ideally like to get the items count for each owner without a separate query.

foreach ($owner as $owner) {
    $owner->getItems()->count();
}