Php – Doctrine: Eager load the relationships of a lazy relationship

doctrinedoctrine-ormlazy-loadingormPHP

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!

Best Answer

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:

enter image description here

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

Related Solutions

Eager loading of lazy loaded entities in nHibernate using ActiveRecord

Do an "inner join fetch" on the Account.Order.Product entity. So instead of something like this (which is what you probably already have):

"from Account a inner join fetch a.Order where a.ID = ?"

Tell it to fetch the Order.Product as well:

"from Account a inner join fetch a.Order inner join fetch a.Order.Product where a.ID = ?"

Java – Difference between FetchType LAZY and EAGER in Java Persistence API

Sometimes you have two entities and there's a relationship between them. For example, you might have an entity called University and another entity called Student and a University might have many Students:

The University entity might have some basic properties such as id, name, address, etc. as well as a collection property called students that returns the list of students for a given university:

public class University {
   private String id;
   private String name;
   private String address;
   private List<Student> students;

   // setters and getters
}

Now when you load a University from the database, JPA loads its id, name, and address fields for you. But you have two options for how students should be loaded:

To load it together with the rest of the fields (i.e. eagerly), or
To load it on-demand (i.e. lazily) when you call the university's getStudents() method.

When a university has many students it is not efficient to load all of its students together with it, especially when they are not needed and in suchlike cases you can declare that you want students to be loaded when they are actually needed. This is called lazy loading.

Here's an example, where students is explicitly marked to be loaded eagerly:

@Entity
public class University {

    @Id
    private String id;

    private String name;

    private String address;

    @OneToMany(fetch = FetchType.EAGER)
    private List<Student> students;

    // etc.    
}

And here's an example where students is explicitly marked to be loaded lazily:

@Entity
public class University {

    @Id
    private String id;

    private String name;

    private String address;

    @OneToMany(fetch = FetchType.LAZY)
    private List<Student> students;

    // etc.
}

Best Answer

Related Solutions

Eager loading of lazy loaded entities in nHibernate using ActiveRecord

Java – Difference between FetchType LAZY and EAGER in Java Persistence API

Related Topic