Doctrine select many from the one (many-to-one unidirectional (different bundles))

Question

Working on a legacy project which restricts the options available, has left me in a situation where I need to solve the following problem, ideally with doctrine.

I have two entities in different bundles that have a unidirectional many-to-one link.

BundleA has dependency on BundleB and the entities are linked similar to this:

BundleA/Entity/TheMany:

/**
 * @var TheOne $theOne
 * @ORM\ManyToOne(targetEntity="BundleB\Entity\TheOne")
 * @ORM\JoinColumn(name="theone_id", referencedColumnName="id", onDelete="SET NULL")
 * 
 */
private $theOne;

From BundleB I now need to select all TheOne entities, and for each I need all of the TheMany entities.

The query also needs to be sortable on any property of TheOne entity, or the count of related TheMany entities.

It is fairly simple in Doctrine to build a query which brings back all TheOne entities and one of TheMany for each... however I am having some difficulty coming up with a Doctrine query that will bring back all of the related TheMany entities rather than just one.

I was hoping someone might have encountered a similar issue and therefore have some insight?

This may not have been explained clearly enough, in which case please direct me to explain further.

Answer 1

In the end I was able to achieve what I needed by using GROUP_CONCAT (which required inclusion of https://github.com/beberlei/DoctrineExtensions).

The query looks something like this:

    $queryBuilder->select(
        'to,
        GROUP_CONCAT(DISTINCT tm.id SEPARATOR \',\') as theManyIds, 
        COUNT(DISTINCT tm.id) as HIDDEN theManyCount'
    )
        ->from('BundleB\Entity\TheOne', 'to')
        ->leftJoin(
            'BundleA\Entity\TheMany',
            'tm',
            Join::WITH,
            'to.id = tm.theOne'
        )
        ->groupBy('to.id')
        ->orderBy($sortString, $direction)
        ->setFirstResult($start)
        ->setMaxResults($limit);

I compromised by accepting the consequences of linking the two bundles - however that could have been avoided by making use of Native SQL and Result Set Mapping (http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/native-sql.html).