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

Question

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!

abdiel abdiel · Accepted Answer · 2016-07-30T13:51:29

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.

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

3 Answers