Doctrine: Eager load the relationships of a lazy relationship?

Question

I am using Doctrine 2 to map an academic schedule. Here's a simplified look at the relationships:

• A class has events (one-to-many)
  • An event has a type (many-to-one)
  • An event has a location (many-to-one)

Using $em->find() I can fetch the class only, with lazy-loaded relations. Or using DQL with joins, I can eager load the entire object graph. Is there a solution that's in-between?

I want to fetch a class with lazy-loaded relations, and then under certain conditions trigger an eager load of the event relationship and all the event properties. So when I call something like $class->getEventsHydrateAll(), all of the events and event types and event locations will be hydrated at once.

I think I could accomplish this by updating my event schema to flag the type and location relationships as fetch="EAGER". But I'd like to have control over when this deep hydration occurs.

Here's one attempt I made in my class repository, but Doctrine is running individual queries to lookup each type and location.

$query = $this->_em->createQuery('
        SELECT c FROM My\Entity\Class c
        WHERE c.id = :classId
');
$query->setParameter('classId', $classId)
      ->setFetchMode('My\Entity\Event', 'type', 'EAGER')
      ->setFetchMode('My\Entity\Event', 'location', 'EAGER');

try {
    return $query->getSingleResult();
} catch (\Doctrine\ORM\NoResultException $e) {
    return NULL;
}

Does anyone know if Doctrine supports this? Thanks!

Answer 1

TL;DR:

You can use the EAGER flag on an class property to have it load it's relationships eagerly. http://doctrine-orm.readthedocs.org/en/latest/reference/annotations-reference.html#manytoone

I'm not sure if this will help you, but this is how I solved it.

First a bit of background for my situation. I'm currently creating an OAuth 2 implementation on which I want fine grain control over the scopes. Scopes are quite granular to star with for (e.g. email, username, etc), then you can set individual permissions on each of them each for read, create, edit and delete.

Here is a database diagram loosely showing that relationship:

So my issue was, how do I, for example, see if a particular token is allowed to read (permission) a username (scope)?

If I load the token, then get all of it's permissions, then foreach read permission, I check for the username scope, then that's a lot of database access.

Test code:

$permissions = $this->getOAuthHelper()
                    ->getAccessToken($accessToken)
                    ->getPermissions();
$results = [];
foreach ($permissions as $permission) {
    $results[] = $permission->getScope()->getTitle();
}
return $results;

Query log:

150630 10:49:47    54 Connect   root@localhost on api
150630 10:49:51    54 Query     SELECT t0.id AS id1, t0.token AS token2, t0.token_expiration AS token_expiration3, t0.refresh AS refresh4, t0.created_at AS created_at5, t0.deleted_at AS deleted_at6, t0.user_id AS user_id7, t0.client_id AS client_id8 FROM oauth_access_tokens t0 WHERE t0.token = 'user-test-token' LIMIT 1
                   54 Query     SELECT t0.id AS id1, t0.access_permission AS access_permission2, t0.scope_id AS scope_id3 FROM oauth_permissions t0 INNER JOIN oauth_access_to_permissions ON t0.id = oauth_access_to_permissions.permission_id WHERE oauth_access_to_permissions.access_id = '1'
150630 10:49:52    54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '1'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '2'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '3'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '4'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '5'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '6'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '7'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '8'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '9'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '10'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '11'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '12'
                   54 Query     SELECT t0.id AS id1, t0.title AS title2, t0.brief AS brief3, t0.category_id AS category_id4 FROM oauth_scopes t0 WHERE t0.id = '13'
                   54 Quit

However, we can see here that the bulk of this is collecting the scopes attached to the permissions. We can use the eager flag on the relationship from permission to scope to grab the scopes with the permissions:

/**
 * @var Scope
 *
 * @ORM\ManyToOne(targetEntity="OAuthScope", fetch="EAGER")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="scope_id", referencedColumnName="id")
 * })
 */
protected $scope;

Note the fetch="EAGER" flag on the ManyToOne annotation.

Now if we run the exact same code:

150630 11:00:06    55 Connect   root@localhost on api
150630 11:00:10    55 Query     SELECT t0.id AS id1, t0.token AS token2, t0.token_expiration AS token_expiration3, t0.refresh AS refresh4, t0.created_at AS created_at5, t0.deleted_at AS deleted_at6, t0.user_id AS user_id7, t0.client_id AS client_id8 FROM oauth_access_tokens t0 WHERE t0.token = 'user-test-token' LIMIT 1
150630 11:00:11    55 Query     SELECT t0.id AS id1, t0.access_permission AS access_permission2, t0.scope_id AS scope_id3, t4.id AS id5, t4.title AS title6, t4.brief AS brief7, t4.category_id AS category_id8 FROM oauth_permissions t0 LEFT JOIN oauth_scopes t4 ON t0.scope_id = t4.id INNER JOIN oauth_access_to_permissions ON t0.id = oauth_access_to_permissions.permission_id WHERE oauth_access_to_permissions.access_id = '1'
                   55 Quit