Inherited Entity with self-referencing ManyToMany: EXTRA_LAZY fetch mode not working

Question

I have to following setup: A parent class

/**
 * @ORM\Entity()
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn(name="discr", type="string")
 */
abstract class DataCategory
{
    /**
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    //...
}

and several derived classes which hold references to the parent (only showing here one)

/**
 * @ORM\Entity
 */
class MultiCompoundDataCategory extends DataCategory
{
    /**
     * @ORM\ManyToMany(targetEntity="DataCategory", fetch="EXTRA_LAZY")
     * @ORM\JoinTable(name="multi_compound_data_category_data_category",
     *      joinColumns={@ORM\JoinColumn(name="multi_compound_data_category", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="data_category", referencedColumnName="id")})
     */
    public $summands;


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

Now when loading all MultiCompoundDataCategories via $this->getDoctrine()->getRepository(MultiCompoundDataCategory::class)->findAll(), not only one select on MultiCompoundDataCategories is issued (with a join on the ManyToMany-table). Instead I get one main query, followed by one query for each summand, each with a big fat sequence LEFT JOIN (also the docs contain a warning about this problem, I am massively referencing non-leaf nodes of the inheritance tree).

BTW: Wouldn't LAZY fetching already suffice to avoid loading the ManyToMany ?

From this comment, I assume the problem is that my child entities refencence the parent one. How can I circumvent this? MappedSuperclass?

Maybe the abstract superclass is a problem? – olidem Oct 10 '18 at 09:22 — olidem, Oct 10 '18 at 09:22

score 1 · Answer 1 · answered Oct 11 '18 at 12:01

Ok, I found something but don't know if there exists a better solution.

This blog post gave me inspriation to a solution (last paragraph).

By performing a DQL fetch join, I can eagerly load a child entity with hydrated references. So what I do is to load all child classes.

$all=[];
$all['sampled'] = $em->createQuery("SELECT s FROM ".SampledDataCategory::class." s")->getResult();
$all['compound'] = $em->createQuery("SELECT c, s, s2 FROM ".CompoundDataCategory::class." c JOIN c.cat1 s JOIN c.cat2 s2")->getResult();
$all['unary'] = $em->createQuery("SELECT u, s FROM ".UnaryDataCategory::class." u JOIN u.dataCategory s")->getResult();
$all['multi'] = $em->createQuery("SELECT m, s FROM ".MultiCompoundDataCategory::class." m JOIN m.summands s")->getResult();

After that, the many SELECT queries for every single referenced DataCategory are not necessary, because the entity manager has seen it already.

This sounds weird, but up to now my tests indicate that 4 queries with lots of hydration workload run much faster than ~800 single selects.

Inherited Entity with self-referencing ManyToMany: EXTRA_LAZY fetch mode not working

1 Answers1