Optional association of Doctrine entities with partially null composite references

user_3480271 0
Viewed 80

How to associate Doctrine entities with each other so, that there are optional references between the two so that only part of the composite reference is null?

Consider three tables and their Doctrine entities:

Company

id | name
---+-----
01 | Foo
02 | Bar

Position

id | company_id | name
---+------------+------------
01 |         01 | Chef
02 |         01 | Waitress
01 |         02 | Captain
02 |         02 | Mechanic

User

id | company_id | main_position_id | alternate_position_id
---+------------+------------------+-----------------------
01 |         01 |               01 |                  null
02 |         01 |               02 |                  null
03 |         01 |               02 |                  null
04 |         01 |               01 |                    02
05 |         02 |               01 |                    02
06 |         02 |               02 |                  null
07 |         02 |               02 |                  null

All three have Doctrine entities mapped for them:

use Doctrine\ORM\Mapping as ORM;

class Company {
 /**
  * @ORM\Id()
  * @ORM\GeneratedValue(strategy="NONE")
  * @ORM\Column()
  */
 private $id;

 /**
  * @ORM\Column()
  */
 private $name;
}

Position

class Position {
 /**
  * @ORM\Id()
  * @ORM\GeneratedValue(strategy="NONE")
  * @ORM\Column()
  */
 private $id;

 /**
  * @ORM\Id()
  * @ORM\GeneratedValue(strategy="NONE")
  * @ORM\Column()
  */
 private $company_id;

 /**
  * @ORM\ManyToOne(TargetEntity="Company")
  * @ORM\JoinColumn(name="company_id", referencedColumnName="id")
  */
 private $company;


 /**
  * @ORM\Column()
  */
 private $name;
}

User

class User {
 /**
  * @ORM\Id()
  * @ORM\GeneratedValue(strategy="NONE")
  * @ORM\Column()
  */
 private $id;

 /**
  * @ORM\Column()
  */
 private $company_id;

 /**
  * @ORM\Column()
  */
 private $main_position_id;

 /**
  * @ORM\Column()
  */
 private $alternate_position_id;

 /**
  * @ORM\ManyToOne(TargetEntity="Position")
  * @ORM\JoinColumns(
  *  @ORM\JoinColumn(name="company_id", referencedColumnName="company_id")
  *  @ORM\JoinColumn(name="main_position", referencedColumnName="id")
  * )
  */
 private $main_position;

 /**
  * @ORM\ManyToOne(TargetEntity="Position")
  * @ORM\JoinColumns(
  *  @ORM\JoinColumn(name="company_id", referencedColumnName="company_id")
  *  @ORM\JoinColumn(name="alternate_position", referencedColumnName="id", nullable=true)
  * )
  */
 private $alternate_position;

}

Above works as expected for $main_position and but will fail with OutOfBoundsException "Missing value for primary key id on Position" if $alternate_position_id is null. Database structure is given and cannot be altered.

Expectation is that $alternate_position should either be valid Position entity if defined, or null if the value in database is null.

Update: As a workaround I added a PostLoad() LifeCycleEvent to load the associations on the fly, if needed, but that still comes with an unfortunate performance impact.

Update: So far the cleanest option seems to be to remove the @ORM\ManyToOne(targetEntity="xx") and replace it with @ORM\Column(). This will prevent associations from hydrating, and thus throwing, when entity is instantiated and will allow the getters to load the associated entities on the fly. Downside of this is that it does require access to the entity manager. One option to access EntityManager is to add a "PostLoad" Doctrine Lifecycle Event to the entity:

/** @ORM\PostLoad() */
public function postLoadHandler(LifecycleEventArgs $event) {
 $this->positionRepository=$event->getEntityManager()->getRepository(Position::class);
}

public function getAlternatePosition() {
 return $this->positionRepository->findOneBy(['company_id' => $this->company_id, 'id' => $this->alternate_position_id]);
}

This does come with significant performance impact, though.

doctrine-orm
1 Answers

As you know, your tables are using a composite primary/foreign keys. So if you use

  * @ORM\JoinColumns(
  *  @ORM\JoinColumn(name="company_id", referencedColumnName="company_id")
  *  @ORM\JoinColumn(name="alternate_position", referencedColumnName="id", nullable=true)
  * )

And the values of the columns "alternate_position" and "company_id" are null and "01" respectively, Doctrine will look for a row with the following values:

id   | company_id | name
-----+------------+------------------------------
null |         01 | <Name value in your database>

Even so, you can use criteria API to filter the values and return your expected value:

<?php
use Doctrine\Common\Collections\Criteria;
class User
{
    //...More code

    /**
     * @ORM\ManyToOne(TargetEntity="Position")
     */
    private $alternate_position;

    public function getAlternatePosition()
    {
        $criteria = Criteria::create();
        $criteria = $criteria->leftJoin(...);
        $criteria = $criteria->where(...);
        return $this->alternate_position->matching($criteria);
    }

}

References:

user_54612250
Related