Choose owning side in OneToOne relation

Many times I’ve come got to a situation where I have a unusual high query count. When I inspect the queries in the Symfony profiler I can see that Doctrine is fetching objects I have not requested. To give you a clear image of the problem I’ll show an example of the database mapping.


class User {
  /**
  * @ORM\Column(name="id", type="integer")
  * @ORM\Id
  * @ORM\GeneratedValue(strategy="AUTO")
  */
  private $id;

  /**
  * @ORM\OneToOne(targetEntity="Resume", mappedBy="user")
  */
  private $resume;
}

class Resume {
  /**
  * @ORM\Column(name="id", type="integer")
  * @ORM\Id
  * @ORM\GeneratedValue(strategy="AUTO")
  */
  private $id;

  /**
  * @ORM\OneToOne(targetEntity="User", inversedBy="resume")
  */
  private $user;
}

This will create SQL like this:

CREATE TABLE Resume (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, UNIQUE INDEX UNIQ_6602EC1AA76ED395 (user_id), PRIMARY KEY(id))
CREATE TABLE User (id INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(id))
ALTER TABLE Resume ADD CONSTRAINT FK_6602EC1AA76ED395 FOREIGN KEY (user_id) REFERENCES User (id)

The relation information is on the Resume table because the Resume entity is on the owing side.

Fetching the relation eagerly

As a consequence of Resume being on the owning side of the OneToOne relation it will always be fetched eagerly. You can not fetch it as lazy (default for other relations) or extra lazy. This means that every time you fetch the user entity Doctrine will make a second query to fetch the Resume object. This could be the desired behavior with some relations but not in this case. It is reasonable to think that most of the the you may use the User object without the Resume.

Owning Side and Inverse Side

There is a few things to know about the owning side and the inverse side.

  • The owning side has the inversedBy attribute.
  • The inverse side has the mappedBy attribute.
  • Doctrine will only check the owning side of an association for changes.
  • The owning side of a OneToOne association is the entity with the table containing the foreign key

Source: Doctrine docs

The fix

The fix for this problem is simple. You just change the owning side and inverse side. Make the User entity the owning entity. It makes sense to eagerly fetch the User when you fetch a Resume, right?

So, the rule of thumb here is:

The entity that owns the relation does not need the relation. The inverse side is depended on the relation and will fetch it’s owner.

 

5 Comments

  1. But then you could have lots of NULL fields on the owning side (user), unless every User has a Resume. It doesn’t seem natural to have the FK on the User to me. One way to get around the extra queries is to manually JOIN the Resume record in when you are loading a User. That way Doctrine won’t have to fetch it individually.

    Reply
    • Yes, It might be null on the $user->getResume(). But that is okey.

      I have looked into the solution of manually JOIN the Resume entity when I’m loading the user. It is quite some work with the User entity because you have to dig into the Security component. But anyhow, I don’t want to spend any memory on extra entities when I know I’m not going to use them.

      So instead of working around Doctrine, I choose to reconfigure my entities in a way that Doctrine has intended.

    • Call me weird, but what does the User Entity have to do with the security component? Unless you store your user entity in the session as user object, they are not related… And I really hope you’re not storing your Entity in your session ;)

    • hehe. No, Im not storing my User *entity* in the session. =) But of course you store some keys to be able to know which user is logged in.

      If I wanted to do like @Brian suggested I need to make sure to add the extra JOIN somewhere. I have to implement my own UserProvider and make sure it extends Symfony\Component\Security\Core\User\UserProviderInterface. Because it is the UserProvider that loads the User object at each request.

    • When you use the Security to fetch a user you get back generally only the User entity, and if you have OneToOne relationships, doctrine will do additional ones. I had this same problem and solved the same way, for me it was more disturbing, when I just wanted to list users, but I didn’t needed the – in this case called Resumse – additional info, but after 10 user in the list, doctrine did 10 more additional query, and I simply didn’t wanted to fetch those info with a join cause I didn’t needed them.

Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>