Symfony doctrine join related entities to discriminator columns

Question

RoundMatch.php

/**
 * @ORM\Entity(repositoryClass="MyApp\MyBundle\Repository\RoundMatchRepository")
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn(name="type", type="string")
 * @ORM\DiscriminatorMap({"team_round_match" = "TeamRoundMatch", "player_round_match" = "PlayerRoundMatch"})
 * @ORM\Table("my_round_match")
 */
abstract class RoundMatch
{

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

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="match_date", type="datetime")
     */
    private $matchDate;

How can I join related entities to discriminated entities? I cannot get direct access to discriminated table columns to create joins.

I cannot get access to discriminated table columns to create joins. How can I join children entities to discriminator entities?

I created joins like this:

RoundMatchRepository.php

    public function getMatchesWithNoResultsSubmitted()
    {
            $qb = $this->createQueryBuilder("rm");
            $qb->leftJoin("rm.round", "rnd" )
            ->leftJoin("rnd.group", "sg")
            ->leftJoin("sg.server", "ss")
            ->leftJoin("ss.stage", "ts")
            ->leftJoin("ts.tournament", "t")
            ->leftJoin("MyAppMyBundle:PlayerRoundMatch", "prm", "WITH", "rm.id = prm.id")
            ->leftJoin("prm.player1", "p1")
            ->leftJoin("prm.player2", "p2")
            ->leftJoin('p1.gameProfiles',"gp1")
            ->leftJoin('p2.gameProfiles',"gp2")
            ->leftJoin('p1.gameProfiles', 'gp1', "WITH", $qb->expr()->andX(
                    $qb->expr()->eq('t.game', 'gp1.game'),
                    $qb->expr()->eq('prm.player1', 'gp1.player')
                ))
            ->leftJoin('p1.gameProfiles', 'gp2', "WITH", $qb->expr()->andX(
                    $qb->expr()->eq('t.game', 'gp2.game'),
                    $qb->expr()->eq('prm.player2', 'gp2.player')
                ));
                return $qb->getQuery()->getResult();
}

I want to use result object in a twig and I cannot get joined entities in returned object because they are not joined via object relation. I don't have object relation created because they are joined one to one via discriminator.

Possible duplicate of [Doctrine 2 - How to use discriminator column in where clause](https://stackoverflow.com/questions/5988636/doctrine-2-how-to-use-discriminator-column-in-where-clause) — LBA, Oct 27 '17 at 08:13
Looking more at your question. Personally, I don't think table inheritance is the answer for your requirements. A match should probably have its own record with the date. Then just simple relationships `match` <- `players`, `match` <- `team`, `player` <-> `team`. — lukeocodes, Oct 27 '17 at 08:59

lukeocodes · Answer 1 · 2017-10-27T08:44:54.803

I have achieved dynamic relationships using Single Table Inheritance. This will allow you to write DQL against the relation or against the discriminating class.

You CANNOT "query" the discriminator using DQL. You must INSTANCE OF the entity name, see possible duplicate as per @LBA's comment

In this resulting schema, group_id will mean a different relation depending on the discriminator.

Obviously, this has performance implications on the database, though.

/**
 * @ORM\Entity
 * @ORM\InheritanceType("SINGLE_TABLE")
 * @ORM\DiscriminatorColumn(name="type", type="string", length=255)
 * @ORM\DiscriminatorMap(
 *   {"parents" = "Parents", "children" = "Child"}
 * )
 * @ORM\Table("people")
 */
class Person
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var mixed
     */
    private $group;
}

/*
 * @ORM\Table("parents")
 */
class Parent extends Person
{
    /**
     * Many parents have one group.
     *
     * @ManyToOne(targetEntity="ParentGroups", inversedBy="parents")
     * @JoinColumn(name="group_id", referencedColumnName="id")
     */
    private $group;
}

/*
 * @ORM\Table("children")
 */
class Child extends Person
{
    /**
     * Many children have one group.
     *
     * @ManyToOne(targetEntity="ChildGroups", inversedBy="children")
     * @JoinColumn(name="group_id", referencedColumnName="id")
     */
    private $group;
}

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

    /**
     * One group has many parents.
     *
     * @OneToMany(targetEntity="Parent", mappedBy="group")
     */
    private $parents;

    public function __construct() {
        $this->parents = new ArrayCollection();
    }
}

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

    /**
     * One group has many parents.
     *
     * @OneToMany(targetEntity="Child", mappedBy="group")
     */
    private $children;

    public function __construct() {
        $this->children = new ArrayCollection();
    }
}

Effectively the extending tables can override the annotations (if any) of the parent.

It work's a dream and we have multiple uses of this on our monolith :)

Untested code, but it should give you an idea of how I achieved it. If you struggle from here I'll go and make sure it runs.

Symfony doctrine join related entities to discriminator columns

1 Answers1