Doctrine fetching indirectly associated objects

Question

I am trying to fetch associated objects through 3 tables (user, client, account). User has a one-to-many relationship to client, client has a one-to-many relationship to account. I easily fetch all the clients for one specific user using this simple code:

$user = $this->getUser();
$id = $user->getId();
$user = $this->getDoctrine()->getRepository('AcmeUserBundle:User')->find($id);
$clients = $user->getClients();

Similarly, I have no issue retrieving all accounts for one specific client using this code:

$client = $this->getDoctrine()->getRepository('AcmeUserBundle:Client')->find($clientref);
$accounts = $client->getAccounts();

Now I want to obtain directly all accounts related to one user. How do I do so? I tried the following:

$user = $this->getUser();
$id = $user->getId();
$user = $this->getDoctrine()->getRepository('AcmeUserBundle:User')->find($id);
$client = $user->getClients();
$accounts = $client->getAccounts();

But I get the following error 'Attempted to call method "getAccounts" on class "Doctrine\ORM\PersistentCollection"'. I believe I am missing something because "Accounts" may not be lazy loaded by Doctrine when I fetch the user, only "Clients" are. What is the best way to achieve this? Could you give me an example of code that would work (e.g. iteration or DQL query)? Thanks.

Answer 1

That's correct behavior. A collection is returned by $user->getClients(), not a single object. Doctrine's collections do not proxy method calls to their members. There are two ways to solve your problem:

The simpler one. Rely on Doctrine's lazy load. Let's say you use data like this:

foreach ($client as $user->getClients()) {
    foreach ($account as $client->getAccounts()) {
        echo $account->getId();
    }
}

ORM will fetch accounts automatically, running a separate query to database for each client. This will do only if you don't care about performance: O(N) requests are bad.

The better one. Use LEFT JOIN to get all the data with one query to database.

$qb = $this->getDoctrine()->getManager()->createQueryBuilder()
    ->select('u, c, a')
    ->from('AcmeUserBundle:User', 'u')
    ->leftJoin('u.clients', 'c')
    ->leftJoin('c.accounts', 'a')
    ->where('u.id = :id');

$user = $qb->getQuery()
    ->setParameter('id', $id)
    ->getOneOrNullResult();