Symfony Association Mapping OneToOne and OneToMany to Same Entity

Question

I have a View entity that represents the primary page record, and then I have an associated entity called ViewVersion which stores multiple versions of the entity as it's changed over time. The View entity sets the current "Published" ViewVersion in the VersionId field. This makes for a simple OneToOne association. But in some contexts I will also want to get all the versions associated with this View entity, e.g. if I want to allow the user to review older versions and revert back. So I will need another mapping which is a OneToMany. The first viewVersion will map to the active "published" version, and the second viewVersions will show all the versions.

Entity Definitions

/**
* @ORM\Entity
* @ORM\Table(name="view")
* @ORM\Entity(repositoryClass="Gutensite\CmsBundle\Entity\View\ViewRepository")
*/
class View extends Entity\Base {

    /**
    * @ORM\OneToOne(targetEntity="\Gutensite\CmsBundle\Entity\View\ViewVersion", inversedBy="view", cascade={"persist", "remove"}, orphanRemoval=true)
    * @ORM\JoinColumn(name="versionId", referencedColumnName="id")
    */
    protected $viewVersion;

    /**
     * @ORM\Column(type="integer", nullable=true)
     */
    protected $versionId = NULL;

    /**
 * @ORM\OneToMany(targetEntity="\Gutensite\CmsBundle\Entity\View\ViewVersion", mappedBy="viewAll", cascade={"persist", "remove"}, orphanRemoval=true)
 */
protected $viewVersions;
}

/**
* @ORM\Entity
* @ORM\Table(name="view_version")
* @ORM\Entity(repositoryClass="Gutensite\CmsBundle\Entity\View\ViewVersionRepository")
*/
class ViewVersion extends Entity\Base {

    /**
    * @ORM\OneToOne(targetEntity="\Gutensite\CmsBundle\Entity\View\View", mappedBy="viewVersion", cascade={"persist"})
    */
    protected $view;

    /**
    * @ORM\ManyToOne(targetEntity="\Gutensite\CmsBundle\Entity\View\View", inversedBy="viewVersions")
    * @ORM\JoinColumn(name="viewId", referencedColumnName="id")
    */
    protected $viewAll;

    /**
    * The primary view entity that this version belongs to.
    * @ORM\Column(type="integer", nullable=true)
    */
    protected $viewId;

}

This "works" but is it recommended to have two associations with the same entity like this? Or is this a really bad idea?

The ViewVersion entity will reference a single View entity in both cases, but the mapped associations need two separate variables, e.g. View and ViewAll. I'm not exactly sure how the internals work for the association, and how the reference variable with the mapping is used.

Alternatively, I could get rid of the OneToOne association, and just set a ViewRepository function to get the current published version based on the versionId (just like the old mapped entity used to do with the getVersion()). That would work, but is it more internal overhead, because it would make two queries... or will Doctrine be smart enough to optimize this, just like it did with the getVersion().

NOTE: These other answers are not complete.

References: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/working-with-associations.html http://doctrine-orm.readthedocs.org/en/2.0.x/reference/association-mapping.html#one-to-many-bidirectional

Answer 1

Typically, I have found the best approach is to solve this in a different way.

One common pattern I have seen before is you use a single table to hold all records, and have an 'active' flag.

If your query to select the active one works like so:

SELECT * FROM table WHERE active = true ORDER BY updated_at DESC LIMIT 1;

Then enabling a new one becomes as simple as:

UPDATE table SET active = 1, updated_at = '<timestamp>' WHERE id = <new id>;
UPDATE table SET active = 0, updated_at = '<timestamp>' WHERE id = <old id>;

Your new page will be active as soon as the first query hits, and your second query will avoid any sort of weirdness as that row will already be no longer active.

If you have other models that depend on a consistent ID to reference, then another route which also maintains some sanity would be to have one table for the active entries (in whole, not in part) and then a second table with additional metadata to track versions.

The latter approach could be nicely handled via Doctrine's inheritance system (http://docs.doctrine-project.org/en/2.0.x/reference/inheritance-mapping.html) which would let you define the base View class, and then for the "ViewRevision" model, extend View and add a "Revised on" type timestamp.

Answer 2

Per the advice from @jmather I've decided this model is "okay", because I need a single View entity that other entities can access (e.g. Routing urls that point to a single View, i.e. "page").

I've changed the OneToOne relationship for View to be unidirectional only, because the ViewVersion already has an association back to the View via the other OneToMany (so it doesn't need two paths back).

This allows me to keep a simple method for $view->getPublished() handy and seems more logical.

/**
* @ORM\Entity
* @ORM\Table(name="view")
*/
class View extends Entity\Base {

    /**
    * This is a OneToOne Unidirectional association, just so that we can get the
    * current published version easily, based on the publishedId.
    * @ORM\OneToOne(targetEntity="\Gutensite\CmsBundle\Entity\View\TestVersion")
    * @ORM\JoinColumn(name="publishedId", referencedColumnName="id")
    */
    protected $published;

    /**
     * @ORM\Column(type="integer", nullable=true)
     */
    protected $publishedId = NULL;

    /**
     * This is the regular OneToMany Bi-Directional Association, for all the versions.
 * @ORM\OneToMany(targetEntity="\Gutensite\CmsBundle\Entity\View\ViewVersion", mappedBy="view", cascade={"persist", "remove"}, orphanRemoval=true)
 */
protected $versions;
}

/**
* @ORM\Entity
* @ORM\Table(name="view_version")
*/
class ViewVersion extends Entity\Base {


    /**
    * @ORM\ManyToOne(targetEntity="\Gutensite\CmsBundle\Entity\View\View", inversedBy="versions")
    * @ORM\JoinColumn(name="viewId", referencedColumnName="id")
    */
    protected $view;

    /**
    * The primary view entity that this version belongs to.
    * @ORM\Column(type="integer", nullable=true)
    */
    protected $viewId;

}

However, I've discovered that as long as the $view->publishedId is set the view can't be deleted from the database because of foreign key constraints (even though it's uni-directional). So I have to break that foreign key link before removing. I think that's fine. I posted details about that here: Overlapping Entity Association causing Database Foreign Key Constraint Errors when Removing Entity