Single table inheritance and indexes

Hi,

I have an entity that uses single table inheritance so we are able to extend it within different projects...

However whenever I define new / additional indexes in the extended entity they are getting ignored?

Here is the code for my two entities.

Thanks,

Harry

Base:

/**
 * @ORM\Entity
 * @InheritanceType("SINGLE_TABLE")
 *
 * @ORM\HasLifecycleCallbacks
 * @ORM\Table(name="property_data", indexes={
 *      @ORM\Index(name="listing_status", columns={"listing_status"}),
 *      @ORM\Index(name="reference", columns={"reference"}),
 *      @ORM\Index(name="listing_type", columns={"listing_type"}),
 *      @ORM\Index(name="status", columns={"status"}),
 *      @ORM\Index(name="council_id", columns={"council_id"}),
 *      @ORM\Index(name="county_id", columns={"county_id"}),
 *      @ORM\Index(name="town_id", columns={"town_id"}),
 *      @ORM\Index(name="region_id", columns={"region_id"}),
 *      @ORM\Index(name="postcode_id", columns={"postcode_id"}),
 *      @ORM\Index(name="property_type_id", columns={"property_type_id"}),
 *      @ORM\Index(name="num_living_rooms", columns={"num_living_rooms"}),
 *      @ORM\Index(name="num_bathrooms", columns={"num_bathrooms"}),
 *      @ORM\Index(name="num_bedrooms", columns={"num_bedrooms"})
 * })
 */

Extended:

/**
 * Property
 *
 * @ORM\Entity(repositoryClass="Howsmart\Bundle\PropertyBundle\Entity\PropertyRepository")
 * @ORM\Table(name="property_data", indexes={
 *      @ORM\Index(name="listing_status", columns={"listing_status"}),
 *      @ORM\Index(name="reference", columns={"reference"}),
 *      @ORM\Index(name="listing_type", columns={"listing_type"}),
 *      @ORM\Index(name="status", columns={"status"}),
 *      @ORM\Index(name="council_id", columns={"council_id"}),
 *      @ORM\Index(name="county_id", columns={"county_id"}),
 *      @ORM\Index(name="town_id", columns={"town_id"}),
 *      @ORM\Index(name="region_id", columns={"region_id"}),
 *      @ORM\Index(name="postcode_id", columns={"postcode_id"}),
 *      @ORM\Index(name="property_type_id", columns={"property_type_id"}),
 *      @ORM\Index(name="num_living_rooms", columns={"num_living_rooms"}),
 *      @ORM\Index(name="num_bathrooms", columns={"num_bathrooms"}),
 *      @ORM\Index(name="num_bedrooms", columns={"num_bedrooms"}),
 *      @ORM\Index(name="investment_score", columns={"investment_score"}),
 *      @ORM\Index(name="investment_score_accuracy", columns={"investment_score_accuracy"}),
 *      @ORM\Index(name="score_land_property", columns={"score_land_property"}),
 *      @ORM\Index(name="score_transport_infrastructure", columns={"score_transport_infrastructure"}),
 *      @ORM\Index(name="score_education_employement", columns={"score_education_employement"}),
 *      @ORM\Index(name="score_safety_comfort", columns={"score_safety_comfort"}),
 *      @ORM\Index(name="score_lifestyle_leisure", columns={"score_lifestyle_leisure"}),
 *      @ORM\Index(name="agent_id", columns={"agent_id"})
 * })
 */

[Ocramius]

@HarryWiseman what are the ignored indexes in the example?

The ignored indexes are the ones declared in the extended entity "Property"
not "PropertyData"
On 11 Dec 2015 01:40, "Marco Pivetta" notifications@github.com wrote:

@HarryWiseman https://github.com/HarryWiseman what are the ignored
indexes in the example?

—
Reply to this email directly or view it on GitHub
#5543 (comment)
.

[Ocramius]

@HarryWiseman yes, but which ones, exactly? I see for example @ORM\Index(name="listing_status", columns={"listing_status"}), being duplicate, and that is obviously to be ignored.

Ah sorry @Ocramius its all columns from

@ORM\Index(name="investment_score", columns={"investment_score"})

down that are being ignored.

These are the ones in the extended entity. Sorry should have mentioned that before.

[sstalle]

Here is an isolated test case for this:

<?php

namespace Doctrine\Tests\ORM\Mapping;

use Doctrine\ORM\Mapping\ClassMetadataFactory;
use Doctrine\ORM\Mapping\ClassMetadataInfo;

class SingleTableIndexMappingTest extends \Doctrine\Tests\OrmTestCase
{
    /**
     * @var ClassMetadataFactory
     */
    private $cmf;

    /**
     * {@inheritDoc}
     */
    protected function setUp()
    {
        $this->cmf = new ClassMetadataFactory();

        $this->cmf->setEntityManager($this->_getTestEntityManager());
    }

    public function testSingleTableInheritanceIndex()
    {
        $class = $this->cmf->getMetadataFor(__NAMESPACE__ . '\\EntitySingleInheritanceIndexSubClass');
        /* @var $class ClassMetadataInfo */

        $this->assertArrayHasKey('single1', $class->fieldMappings);
        $this->assertArrayHasKey('name', $class->fieldMappings);
        $this->assertArrayHasKey('IDX_SINGLE1_INDEX', $class->table['indexes']);
        $this->assertArrayHasKey('IDX_NAME_INDEX', $class->table['indexes']);
    }
}

/**
 * @Entity
 * @InheritanceType("SINGLE_TABLE")
 * @Table(indexes={@Index(name="IDX_SINGLE1_INDEX", columns={"single1"})})
 * @DiscriminatorColumn(name="type", type="string", length=20)
 * @DiscriminatorMap({
 *     "parent"   = "SingleInheritanceBaseIndex",
 *     "child"    = "EntitySingleInheritanceIndexSubClass",
 * })
 */
class SingleInheritanceBaseIndex
{
    /** @Id @Column(type="integer") */
    private $id;
    /** @Column(type="string") */
    private $single1;
}

/**
 * @Entity
 * @Table(indexes={
 *     @Index(name="IDX_SINGLE1_INDEX", columns={"single1"}),
 *     @Index(name="IDX_NAME_INDEX", columns={"name"})
 * })
 */
class EntitySingleInheritanceIndexSubClass extends SingleInheritanceBaseIndex
{
    /** @Column(type="string") */
    private $name;
}

I think this happens because child settings are overwritten by parent table settings for single table inheritance at https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Mapping/ClassMetadataFactory.php#L202

Does it make sense to add additional indexes in a child class when using a single table inheritance type ?

Imagine that the parent class contains a property mapped with a column named "reference" and no index defined yet for this column. Then we decide in a child class to define an index for the column "reference". The table will have to handle an index that applies only on the rows mapped with the child class.

I thought that inheritance using multiple tables was made especially for that purpose.

[whataboutpereira]

Instead of being ignored, it would be nice if indexes defined in child classes would give a sensible error.

Then again one can manually add those indexes to a migration and it works...