Symfony2/Doctrine2 get joined entities from querybuilder object

Question

Given two doctrine entities (Person and Company), associated one-to-many, and a repository which looks something like this

namespace TestBundle\Entity\Repository;
use Doctrine\ORM\EntityRepository;

class PersonRepository extends EntityRepository {

    public function findAllByAge($age) {
        $qb = $this->createQueryBuilder('p')
                ->select('p','c')
                ->leftjoin('p.company', 'c')
                ->where("p.age = :age")
                ->setParameter('age', $age);

        // ...
    }
}

How could I retrieve the entity (object or name) of the Company, preferably from the $qb object (or from the Alias, DQL, AST, parser, etc)?

Ideally, I would like to have an array containing all the aliases used by the Querybuilder instance, or at least those defined in the select method, together with their entities, in the form:

[
    'p' => 'TestBundle\Entity\Person',
    'c' => 'TestBundle\Entity\Company',
    // etc
]

In $qb->getDQLPart('join') or even something lower level like $qb->getQuery()->getAST()->fromClause->identificationVariableDeclarations there's join information regarding the aliases, but it contains only the Root Entity and its alias (p = TestBundle\Entity\Person).

getRootEntity, getRootAliases, getAllAliases do not help as I get the root entity and/or all aliases, but no way to link them together.

$em->getClassMetadata($rootentity)->associationMappings gives me associations for the root entity, and it contains target entities for joins, but no aliases. I could map the field names to the information from $qb->getDQLPart('join') of course, but that would get me into an area where i'd have to crawl the information recursively from each entity object. That seems like it could cause serious errors.

How does the Querybuilder translate the associations to the right entities? Or does it not do that at all, just parsing to the lower level stuff without ever knowing what entities it is using?

I need the info so I can ensure certain entity fields have specific secondary indexes on them. These secondary indexes can be set using annotations, and are stored in the entity by doctrine ($em->getClassMetadata($entity)->table['indexes']).

I need to know (programmatically) which fields have secondary indexes while building a query, and would prefer staying as high up the abstraction tree as possible.

Answer 1

The way you should do this is quite simple:

namespace TestBundle\Entity\Repository;
use Doctrine\ORM\EntityRepository;

class PersonRepository extends EntityRepository {

    public function findAllByAge($age) {
        $qb = $this->createQueryBuilder('p')
                ->where("p.age = :age")
                ->setParameter('age', $age);

        return $qb->getQuery()->getResult();
    }
}

... then, when walking the response:

$people = $personRepository->findAllByAge($age);
$companies = array_map(function ($person) {
    return $person->getCompany();
}, $people);

I know what you think: wouldn't that create unnecessary requests? Isn't it possible to get all of that in a single SQL call? Well it is indeed possible but it is not as straightforward as this by a long shot.

I wouldn't recommand it unless there is a huge need in performance for that request (like a massive import/export). But since Doctrine already adds an abstraction layer I supposed performance was not an issue here.

EDIT:

Well then in that case the best you could do is to use native queries with custom result set mappers. Because of the way repositories work, regular DQL queries declared within them will always expect to return the entity of the repository (in your case it will try to spit out a Person instance), so I'm afraid there is no real way around it.

It is actually for the better since DQL adds an abstraction layer that is unwelcome in the case of performance-intensive queries.

For long queries I also highly recommand the use of ->iterate(), which will split the request into smaller chunks. In the end your method should look like this:

namespace TestBundle\Entity\Repository;
use Doctrine\ORM\EntityRepository;

class PersonRepository extends EntityRepository
{
    public function getAllByAgeIterator($age)
    {
        $rsm = new ResultSetMappingBuilder($this->_em);
        // RSM configuration here

        $qb = $this->_em->createNativeQuery("
            // Your SQL query here
        ", $rsm)->setParameter('age', $age);

        return $qb->getQuery()->iterate();
    }
}

I did not detail the ResultSetMappingBuilder configuration but I think you will find out just fine.

Answer 2

If I understand correctly, you can return collection of Company objects directly from query, even from PersonRepository:

namespace TestBundle\Entity\Repository;
use Doctrine\ORM\EntityRepository;

class PersonRepository extends EntityRepository {
    public function findAllByAge($age) {
        $qb = $this->getEntityManager()
            ->createQueryBuilder()
            ->from('YourBundle:Person', 'p')
            ->select('c')
            ->distinct()
            ->leftjoin('p.company', 'c')
            ->where("p.age = :age")
            ->setParameter('age', $age);

        // ...
    }
}

I am not sure if returning Company instances from Person repository is good, but it is mainly convention issue. Of course you can always create CompanyRepository::findAllCompaniesWithEployeesAtAge($age) and inverse join like:

namespace TestBundle\Entity\Repository;
use Doctrine\ORM\EntityRepository;

class CompanyRepository extends EntityRepository {
    public function findAllCompaniesWithEployeesAtAge($age) {
        $qb = $this->createQueryBuilder('c')
            ->select('c')
            ->distinct()
            ->leftjoin('c.person', 'p') // or whatever inverse side is called
            ->where("p.age = :age")
            ->setParameter('age', $age);

        // ...
    }
}