Primary key of owning side as a join column

Question

NOTE: Topic is lengthy but detailed and may come in handy if you use Doctrine2 and oneToOne relationships.

Recently I came across a problem in Doctrine:

I created User and UserData objects with oneToOne bidirectional relationship:

User:
...
  oneToOne:
    userdata:
      targetEntity: UserData
      mappedBy: user

UserData:
...
  oneToOne:
    user:
      targetEntity: User
      inversedBy: userdata

So UserData was the owning side with user_id column in it:

user: id, ...
userdata: id, user_id, ...

This created a problem, where every time you fetch a User object (Single user, collection of user or collection of other object with user joined on it) Doctrine would lazy load a UserObject for each User.

Issue described here:

Proposed solution described here:

https://github.com/doctrine/doctrine2/issues/2364

So there are 3 ways around this:

Wait and see if proposed solution is addressed in Doctrine and fixed in future releases (may not happen)
Manually left join UserData to User in every query (still waste of resources, dont need UserData)
Switch inverse side and make User the owning side.

I decided to go with #3. So my schema relationship now looks like this:

User:
...
  oneToOne:
    userdata:
      targetEntity: UserData
      inversedBy: user

UserData:
...
  oneToOne:
    user:
      targetEntity: User
      mappedBy: userdata

This means that my tables now look like this:

user: id, userdata_id, ...
userdata: id, ...

I decided that instead of having Userdata.id autoincremented, I'll set it manually and match it with user.id. This means that UserData.id will always match user.id.

Question Can I use user.id (a primary autoincremented key) as joinColum instead of userdata_id since they will always have the same value? Do you see any potential issues with this way of doing things?

Any other tips or opinions about this issue is greatly welcomed and appreciated.

If it works then great but it seems bit fragile to me. I would just use the normal approach and accept the overhead of having an "extra" column. One initial problem is that you always have to persist and flush User first before persist and flushing UserData to get the id. Kind of a pain. If you found the extra column was really causing an impact then you could always go back and refactor. — Cerad, Mar 24 '12 at 14:42
@Cerad yes, I feel the same way, I think I'll leave the "extra" column. — DavidW, Mar 24 '12 at 18:58

score 18 · Answer 1 · answered Aug 12 '12 at 08:43

18

You could also force partial objects, to get rid off lazy-loading:

use Doctrine\ORM\Query;

//...
$query->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true);

answered Aug 12 '12 at 08:43

Mick

30,759
16
111
130

Thank you for this! This was the only solution that got it down to one SQL query in my case - for some reason even when I did a join it was still lazy-loading the associations right away (but I didn't need those associations anyway, so this is a better solution). – Matt Browne Dec 11 '13 at 00:51
1

what about the findBy , how could i disable the lazy loading in doctrine 2 ( i'm using doctrine 2 with zf2 ) ? – may saghira Dec 08 '14 at 09:41

Chadwick Meyer · Answer 2 · 2014-11-24T21:22:43.910

This is a known issue for OneToOne associations. There is a github discussion about this that is worth reading. A solution (pull request) was proposed and rejected.

Recommendation

Your question suggests the same solution proposed by the contributors to Doctrine: change the owning side of the relationship.

Other Options Explored

I had a similar problem with an entity called Version that had a OneToOne bidirectional relationship with Settings. Every time I queried Version (say for 10 specific version records), Doctrine would do additional queries for the joined Settings (as if it was Lazy Loading these entities). This happened, even though I did not reference Settings anywhere, e.g. $Version->getSettings()->getSomeProperty().

Manual JOIN

The only "solution" (hack) that works for me is to manually included a JOIN for this Settings entity every time I did a query on Version. But since I don't need the extra entity (in this case), that would still be a single extra unnecessary query, every time I query this table in different ways.

Extra Lazy

Based on other suggestions, I thought that if I explicitly specified this relationship as "extra lazy" it would work, e.g.

/**
 * @ManyToMany(targetEntity="Settings", mappedBy="version", fetch="EXTRA_LAZY")
 */

But this doesn't affect hydration. See the Doctrine Docs for more details about what EXTRA_LAZY does.

Hydration Type: HYDRATE_ARRAY

What helped in my case (when I didn't need an entity), was to specify that I wanted to fetch as an array (rather than object).

$query = $queryBuilder->getQuery();
$query->getResult(Query:HYDRATE_ARRAY);

This returns an array, and as a result it doesn't lazy load the OneToOne associations. However, in other contexts where I need the entity object, I had to explicitly JOIN the entity (despite not wanting it).

score 3 · Answer 3 · answered Feb 14 '13 at 22:27

My workaround was to turn the OneToOne relationship into a ManyToOne (with an associated ArrayCollection), with a custom getter and setter which only sets and returns the 0-th element of the collection:

/**
 * Set pref
 *
 * @param \MyBundle\Entity\Pref $pref
 * @return Employee
 */
public function setPref(\MyBundle\Entity\Pref $pref = null) {
    $this->pref[0] = $pref;

    return $this;
}

/**
 * Get pref
 *
 * @return \MyBundle\Entity\Pref 
 */
public function getPref() {
    return $this->pref[0];
}

These methods can (seemingly) be used the same as the ones created by a OneToOne relationship.

doctrine:generate:entities still creates the normal "add" and "remove" methods, but they can be ignored.

Note: I only recently started using these and currently only to read from the database. I don't know of any side effects from this workaround. I'll update this answer if I notice any.

This is admittedly an ugly hack. I hope Doctrine fixes this soon so I can go back to using proper OneToOne relationships.

score 1 · Answer 4 · answered Mar 09 '14 at 20:35

I came across this same problem and remember that the symblog tutorial gave an example of how to reduce the lazy loading by explicitly add left joins on the tables that you do not need. It seems strange to include tables on a join when you do not even want that data at all, but this way you will reduce all of those extra queries down to 1 and it does run faster.

Search for lazy loading - about 1/5 of the way down http://tutorial.symblog.co.uk/docs/customising-the-view-more-with-twig.html

To fix this for the user/userdata issue try adding this to the user repository and use to whenever you need to get all users even if you do not want userdata. It can be further enhanced by selecting partial: ->select('partial p.{user_id,name,}')

   public function getAll($limit = 500) {
       $qb = $this->createQueryBuilder('u')
            ->select('u', 'd')
            ->leftJoin('p.userdata', 'd')
       if (false === is_null($limit))
           $qb->setMaxResults($limit);
     return $qb->getQuery()->getResult();
    }