CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-ormdoctrine
shabang
shabang

Reputation: 170

Too many joins with Doctrine using "Class Table Inheritance" on a Symfony project

I'm working with Symfony and Doctrine and I ended up with the following errors:

SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join`

The main entity (topmost table of the hierarchy) contains a discriminator map of over 61 entities. A "Many-To-Many" association is made over itself, so an Entity can be linked to others as parents or children.

Here is the Entity Doctrine annotation :

/**
 * Entity
 *
 * @ORM\Table(name="entity")
 * @ORM\Entity(repositoryClass="Acme\EntityBundle\Repository\EntityRepository")
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="type", type="string")
 * @DiscriminatorMap({
 *      "sub_entity_1" = "\Acme\SubEntityABundle\Entity\E1",
 *      "sub_entity_2" = "\Acme\SubEntityABundle\Entity\E2",
 *      ...
 * })
 */
class Entity
{
    /**
     * @ORM\ManyToMany(targetEntity="Entity", inversedBy="parentEntities")
     * @ORM\JoinTable(name="rel_entity_entity",
     *   joinColumns={@ORM\JoinColumn(name="parent", referencedColumnName="id")},
     *   inverseJoinColumns={@ORM\JoinColumn(name="child", referencedColumnName="id")}
     * )
     */
    private $childrenEntities;

    /**
     * @ORM\ManyToMany(targetEntity="Entity", mappedBy="childrenEntities")
     *
     */
    private $parentEntities;

    /**
     * Get children entities
     *
     * @return \Doctrine\Common\Collections\Collection
     */
    public function getChildrenEntities()
    {
        return $this->childrenEntities;
    }

    /**
     * Set childrenEntities
     *
     * @param ArrayCollection $entities
     */
     public function setchildrenEntities(ArrayCollection $entities)
     {
         $this->childrenEntities = $entities;
     }

     ...
}

Whenever I use the Query Builder in a formType to get a list of some classic Entities (which do not even extend the topclass "Entity"), Doctrine will do a left join on every entities in the discriminator map (and generate the General error of MySQL).

FormType :

public function buildForm(FormBuilderInterface $builder, array $options)
{
    $builder->add('childrenEntities', 'entity', array(
        'class' => 'AcmeEntityBundle:Entity',
        'required' => false,
        'choice_label' => 'slug', // slug is defined as a main entity's attribute
        'query_builder' => function (EntityRepository $er) {
            return $er->getSomeEntities();
        },
    ));

    ...
}

EntityRepository :

public function getSomeEntities()
{
    return $this->getEntityManager()
        ->createQueryBuilder()
        ->select('e')
        ->from('AcmeEntityBundle:Entity', 'e')
        ->where('e.id IN (:ids)')
        // Tried that, doesn't work
        //->andWhere('e INSTANCE OF AcmeEntityBundle:Entity')
        ->setParameter('ids', [53300, 12345]);
}

Is there a way to tell doctrine not to make any join? I only need main entity's attribute, such an id or a slug, so I don't need any value from sub classes

Doctrine version is : 2.4

Some usefull doc I read :

Thank you!

Upvotes: 2

Views: 2109

Answers (3)

Antony Zh
Antony Zh

Reputation: 302

Looks like you've broken one of the main rules of Doctrine's CTI: not to make an accociation to the class hierarchy. In your case, you've done a parent-child association and that's why Doctrine produced too many JOINS. It tried to JOIN all your children entities to the parents, I guess. I think you should avoid doing a parent-child association with CTI on the same entity.

Upvotes: 0

Mike Simonson
Mike Simonson

Reputation: 681

By the documentation

Class Table Inheritance is an inheritance mapping strategy where each class in a hierarchy is mapped to several tables.

Here it looks like you have some sort of base class and then a bunch of classes that extends it and add some very different properties to it.

I am not super familiar with the Class table inheritance use but here it looks like there is a difference between what the feature was designed for and how you use it.

Upvotes: 0

abdiel
abdiel

Reputation: 2106

Well, big problem because is a mysql limitation. I don't understand why doctrine is doing the left join if you are not requesting that. If you only need data related to the main entity, them change the select to those specific fields:

->select('e.id, e.slug, ...')

Hope this help you.

Upvotes: 0

Related Questions