Useless join statements when using a discriminator map #5208

New Issue

admin · 2026-01-22T15:01:34+01:00

admin commented

2026-01-22 15:01:34 +01:00

Originally created by @sh4bang on GitHub (Aug 2, 2016).

Originally assigned to: @Ocramius on GitHub.

Hi !

I'm working in a big project in which I use a discriminatorMap to handle many entity type : Class Table Inheritance

If we have many mapped entity inside the discriminatorMap (over something like 61 in my case), it generate a MySQL error :

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

The problem is that Doctrine add a join statement for each known entity, even if I don't ask him any field of any sub entity.

My closure that return a QueryBuilder (for my form type), in the repository :

public function getEntityTest()
    {
        return $this->getEntityManager()
            ->createQueryBuilder()
            ->select('t.id, t.slug')
            ->from('AcmeBackendCoreBundle:TestRoot', 't');
    }

Using the above QueryBuilder will generate that kind of SQL :

SELECT 
  t0_.id AS id0, 
  t0_.slug AS slug1 
FROM 
  test_root t0_ 
  LEFT JOIN test_sub1 t1_ ON t0_.id = t1_.id 
  LEFT JOIN test_sub2 t2_ ON t0_.id = t2_.id

But I don't care about test_sub1 or test_sub2 entities... It makes no sense to use it in some cases.

Here is an easy sample to reproduce :

use Doctrine\ORM\Mapping as ORM;
/**
 * TestRoot
 *
 * @ORM\Entity
 * @ORM\Table(name="test_root")
 *
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="type", type="string")
 * @ORM\Entity(repositoryClass="Acme\BackendCoreBundle\Repository\TestRootRepository")
 * @DiscriminatorMap({
 *      "sub_e1" = "\Acme\BackendCoreBundle\Entity\TestSub1",
 *      "sub_e2" = "\Acme\BackendCoreBundle\Entity\TestSub2",
 * })
 */
class TestRoot
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var String
     *
     * @ORM\Column(name="slug", type="string")
     */
    private $slug = "default_slug";

    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set slug
     *
     * @param string $slug
     * @return TestRoot
     */
    public function setSlug($slug)
    {
        $this->slug = $slug;

        return $this;
    }

    /**
     * Get slug
     *
     * @return string 
     */
    public function getSlug()
    {
        return $this->slug;
    }

TestSub1 entity (duplicate for TestSub2 ) :

use Doctrine\ORM\Mapping as ORM;
/**
 * TestSub1
 *
 * @ORM\Entity
 * @ORM\Table(name="test_sub1")
 */
class TestSub1 extends TestRoot
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var String
     *
     * @ORM\Column(name="specific1", type="string")
     */
    private $specific1;

    /**
     * Set specific1
     *
     * @param string $specific1
     * @return TestSub1
     */
    public function setSpecific1($specific1)
    {
        $this->specific1 = $specific1;

        return $this;
    }

    /**
     * Get specific1
     *
     * @return string 
     */
    public function getSpecific1()
    {
        return $this->specific1;
    }
}

Stack Overflow I opened : here

Originally created by @sh4bang on GitHub (Aug 2, 2016). Originally assigned to: @Ocramius on GitHub. Hi ! I'm working in a big project in which I use a discriminatorMap to handle many entity type : [Class Table Inheritance](http://doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/reference/inheritance-mapping.html#class-table-inheritance) If we have many mapped entity inside the discriminatorMap (over something like 61 in my case), it generate a MySQL error : > SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join` The problem is that Doctrine add a join statement for each known entity, even if I don't ask him any field of any sub entity. My closure that return a QueryBuilder (for my form type), in the repository : ``` php public function getEntityTest() { return $this->getEntityManager() ->createQueryBuilder() ->select('t.id, t.slug') ->from('AcmeBackendCoreBundle:TestRoot', 't'); } ``` Using the above QueryBuilder will generate that kind of SQL : ``` sql SELECT t0_.id AS id0, t0_.slug AS slug1 FROM test_root t0_ LEFT JOIN test_sub1 t1_ ON t0_.id = t1_.id LEFT JOIN test_sub2 t2_ ON t0_.id = t2_.id ``` But I don't care about test_sub1 or test_sub2 entities... It makes no sense to use it in some cases. Here is an easy sample to reproduce : ``` php use Doctrine\ORM\Mapping as ORM; /** * TestRoot * * @ORM\Entity * @ORM\Table(name="test_root") * * @InheritanceType("JOINED") * @DiscriminatorColumn(name="type", type="string") * @ORM\Entity(repositoryClass="Acme\BackendCoreBundle\Repository\TestRootRepository") * @DiscriminatorMap({ * "sub_e1" = "\Acme\BackendCoreBundle\Entity\TestSub1", * "sub_e2" = "\Acme\BackendCoreBundle\Entity\TestSub2", * }) */ class TestRoot { /** * @var integer * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /** * @var String * * @ORM\Column(name="slug", type="string") */ private $slug = "default_slug"; /** * Get id * * @return integer */ public function getId() { return $this->id; } /** * Set slug * * @param string $slug * @return TestRoot */ public function setSlug($slug) { $this->slug = $slug; return $this; } /** * Get slug * * @return string */ public function getSlug() { return $this->slug; } ``` **TestSub1** entity (duplicate for **TestSub2** ) : ``` php use Doctrine\ORM\Mapping as ORM; /** * TestSub1 * * @ORM\Entity * @ORM\Table(name="test_sub1") */ class TestSub1 extends TestRoot { /** * @var integer * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /** * @var String * * @ORM\Column(name="specific1", type="string") */ private $specific1; /** * Set specific1 * * @param string $specific1 * @return TestSub1 */ public function setSpecific1($specific1) { $this->specific1 = $specific1; return $this; } /** * Get specific1 * * @return string */ public function getSpecific1() { return $this->specific1; } } ``` Stack Overflow I opened : [here](http://stackoverflow.com/questions/38664540/too-many-joins-with-doctrine-using-class-table-inheritance-on-a-symfony-projec)

admin added the Improvement Invalid labels 2026-01-22 15:01:34 +01:00

admin closed this issue

2026-01-22 15:01:36 +01:00

admin commented

2026-01-22 15:01:36 +01:00

@sh4bang commented on GitHub (Aug 2, 2016):

Even

$query = $em->createQuery('SELECT t FROM AcmeBackendCoreBundle:TestRoot t')->getResult();

or

$query = $em->createQuery('SELECT t FROM AcmeBackendCoreBundle:TestRoot t WHERE t INSTANCE OF AcmeBackendCoreBundle:TestRoot')->getResult();

generates the unwanted JOINS.

@sh4bang commented on GitHub (Aug 2, 2016): Even ``` php $query = $em->createQuery('SELECT t FROM AcmeBackendCoreBundle:TestRoot t')->getResult(); ``` or ``` php $query = $em->createQuery('SELECT t FROM AcmeBackendCoreBundle:TestRoot t WHERE t INSTANCE OF AcmeBackendCoreBundle:TestRoot')->getResult(); ``` generates the unwanted JOINS.

admin commented

2026-01-22 15:01:37 +01:00

@w3sami commented on GitHub (Aug 24, 2016):

see https://github.com/doctrine/doctrine2/issues/5980

@w3sami commented on GitHub (Aug 24, 2016): see https://github.com/doctrine/doctrine2/issues/5980

admin commented

2026-01-22 15:01:38 +01:00

@Ocramius commented on GitHub (Aug 24, 2016):

The problem is that Doctrine add a join statement for each known entity, even if I don't ask him any field of any sub entity.

This is expected behavior.

The JOINS are wanted on doctrine's side. If you ask for a parent entity in a query, you may get any child entity too, regardless of whether you want it or not. From an OOP perspective, this makes sense, since any child entity is also an instance of the parent entity.

Closing as invalid.

To solve this issue on your side:

do not implement everything as an inheritance: that is actively harmful
move to a STI (single table inheritance)
switch to a different RDBMS if so many joins are hit anyway (MySQL's limit has been like that since ages)

@Ocramius commented on GitHub (Aug 24, 2016): > The problem is that Doctrine add a join statement for each known entity, even if I don't ask him any field of any sub entity. This is expected behavior. The JOINS are wanted on doctrine's side. If you ask for a parent entity in a query, you may get any child entity too, regardless of whether you want it or not. From an OOP perspective, this makes sense, since any child entity is also an instance of the parent entity. Closing as `invalid`. To solve this issue on your side: - do not implement everything as an inheritance: that is actively harmful - move to a STI (single table inheritance) - switch to a different RDBMS if so many joins are hit anyway (MySQL's limit has been like that since ages)

admin commented

2026-01-22 15:01:38 +01:00

@juaiglesias commented on GitHub (May 12, 2021):

Is this still the way it's working? Then why this documentation says:

This strategy inherently requires multiple JOIN operations to perform just about any query which can have a negative impact on performance, especially with large tables and/or large hierarchies. When partial objects are allowed, either globally or on the specific query, then querying for any type will not cause the tables of subtypes to be OUTER JOINed which can increase performance but the resulting partial objects will not fully load themselves on access of any subtype fields, so accessing fields of subtypes after such a query is not safe.

@juaiglesias commented on GitHub (May 12, 2021): Is this still the way it's working? Then why [this documentation](https://www.doctrine-project.org/projects/doctrine-orm/en/2.8/reference/inheritance-mapping.html#performance-impact) says: > This strategy inherently requires multiple JOIN operations to perform just about any query which can have a negative impact on performance, especially with large tables and/or large hierarchies. When partial objects are allowed, either globally or on the specific query, then querying for any type will not cause the tables of subtypes to be OUTER JOINed which can increase performance but the resulting partial objects will not fully load themselves on access of any subtype fields, so accessing fields of subtypes after such a query is not safe.

admin commented

2026-01-22 15:01:38 +01:00

@beberlei commented on GitHub (May 12, 2021):

The doc speaks about PARTIAL queries, is that what you are doing? Partial objects are deprecated in any case.

Querying for the root type or a parent type will generally cause these joins. Do not use JTI with many entities

@beberlei commented on GitHub (May 12, 2021): The doc speaks about PARTIAL queries, is that what you are doing? Partial objects are deprecated in any case. Querying for the root type or a parent type will generally cause these joins. Do not use JTI with many entities

admin commented

2026-01-22 15:01:38 +01:00

@juaiglesias commented on GitHub (May 12, 2021):

Yes, I am doing a partial query. So you recommend not using CTI with many entities because this left joins are inevitable even in partial queries (I know they will go deprecated after 2.8.x but I guess an alternative will be provided for them)?

In that case - if it's okay for you - I would like to update the docs (https://github.com/doctrine/orm/blob/2.8.x/docs/en/reference/inheritance-mapping.rst) because there says another thing.

@juaiglesias commented on GitHub (May 12, 2021): Yes, I am doing a partial query. So you recommend not using CTI with many entities because this left joins are inevitable even in partial queries (I know they will go deprecated after 2.8.x but I guess an alternative will be provided for them)? In that case - if it's okay for you - I would like to update the docs (https://github.com/doctrine/orm/blob/2.8.x/docs/en/reference/inheritance-mapping.rst) because there says another thing.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5208