Doctrine2 one-to-one relation auto loads on query

Question

I have a query that looks like this:

My user entity has a one-to-one relation that looks like this:

/**
 * @var UserProfile
 *
 * @ORM\OneToOne(targetEntity="UserProfile",mappedBy="user")
 */
private $userProfile;

Anytime I make a query to select multiple user objects, it creates an additional select statement per user to query for the UserProfile data even though I am not accessing it through a get method. I don't always need the UserProfile data, and I certainly don't want to load this data every single time I'm displaying a list of users.

Any idea why these queries are executed at run time?

Answer 1

Here is solutions explain with details :

https://groups.google.com/forum/#!topic/doctrine-user/fkIaKxifDqc

"fetch" in the mapping is a hint, that is, if it is possible Doctrine does that, but if its not possible, obviously it does not. Proxying for lazy-loading is simply not always possible, technically. The situations where its not possible are:

1) one-to-one from inverse to owning side (appears only in bidirectional one-to-one associations). Precondition a) above can not be met. 2) one-to-one/many-to-one association to a hierarchy and the targeted class has subclasses (is not a leaf in the class hierarchy). Precondition b) above can not be met.

In these cases, proxying is technically not possible.

Your options to avoid this n+1 problem:

1) fetch-join via DQL: "select c,ca from Customer join c.cart ca". Single query but join, however, joins on to-one associations are relatively cheap.

2) force partial objects. No additional queries but also no lazy-load: $query->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true)

3) if an alternative result format (i.e. getArrayResult()) is sufficient for a use-case, these also avoid this problem.

Benjamin had some ideas about automatic batching of these loads to avoid n+1 queries but this does not change the fact that proxying is not always possible.

Answer 2

I spent a lot of time searching for a solution. For me, none of the options were satisfying enough, but maybe I can save someone some time with this list of workarounds:

1) Change the owning side and inverse side http://developer.happyr.com/choose-owning-side-in-onetoone-relation - I don't think that's right from a DB design perspective every time.

2) In functions like find, findAll, etc, the inverse side in OneToOne is joined automatically (it's always like fetch EAGER). But in DQL, it's not working like fetch EAGER and that costs the additional queries. Possible solution is every time to join with the inverse entity

3) If an alternative result format (i.e. getArrayResult()) is sufficient for some use-cases, that could also avoid this problem.

4) Change inverse side to be OneToMany - just looks wrong, maybe could be a temporary workaround.

5) Force partial objects. No additional queries but also no lazy-loading: $query->setHint (Query::HINT_FORCE_PARTIAL_LOAD, true) - seams to me the only possible solution, but not without a price: Partial Objects are a little bit risky, because your entity behavior is not normal. For example if you not specify in ->select() all associations that you will user you can have an error because your object will not be full, all not specifically selected associations will be null

6) Not mapping the inverse bi-directional OneToOne association and either use an explicit service or a more active record approach - https://github.com/doctrine/doctrine2/pull/970#issuecomment-38383961 - And it looks like Doctrine closed the issue

Answer 3

It seems that this is a open issue in Doctrine, see also

4.7.1. Why is an extra SQL query executed every time I fetch an entity with a one-to-one relation?

If Doctrine detects that you are fetching an inverse side one-to-one association it has to execute an additional query to load this object, because it cannot know if there is no such object (setting null) or if it should set a proxy and which id this proxy has. To solve this problem currently a query has to be executed to find out this information.

Source

Answer 4

As @apfelbox explained... there is no fix for it now.

I went for a OneToMany solution in a combination with unique key:

User.php

/**
 * @ORM\OneToMany(targetEntity="TB\UserBundle\Entity\Settings", fetch="EXTRA_LAZY", mappedBy="user", cascade={"all"})
 */
protected $settings;

/**
 * @return \Doctrine\Common\Collections\Collection
 */
public function getSettings()
{
    return $this->settings;
}

And

Settings.php

/**
 * @ORM\ManyToOne(targetEntity="TB\UserBundle\Entity\User", fetch="EXTRA_LAZY", inversedBy="settings")
 * @ORM\JoinColumn(name="user_id", referencedColumnName="id", nullable=false)
 */
protected $user;

And to ensure the uniqueness in Settings.php include:

use Doctrine\ORM\Mapping\UniqueConstraint;

And add unique index

/**
 * @ORM\Entity
 * @ORM\Table(name="user_settings", uniqueConstraints={@UniqueConstraint(name="user", columns={"user_id"})})
 */
class Settings

So when I want to access the user Settings I just need to this (which will fire ONE query ONLY in that specific moment)

$_settings = $user->getSettings()->current();

I think is the cleanest solution.