Orm: Not possible to execute a WHERE condition from a parent entity with discriminators (with InheritanceType JOINED and DiscriminatorMap and DiscriminatorColumn)

[josedacosta]

Hello,

Not possible to execute a WHERE condition from a parent entity with discriminators (with InheritanceType JOINED and DiscriminatorMap and DiscriminatorColumn)

It seems that the persist (and the cascade persist) works:

Https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Persisters/Entity/SingleTablePersister.php#L60

Https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Persisters/Entity/JoinedSubclassPersister.php#L429

The logic of "parent.children.column = 12" should be the same in the WHERE condition

And well understood a WHERE on a column of a LEFT JOIN and is always possible in MySQL, there is no reason that it is not in Doctrine ...

Manual addition of the WHERE in the MySQL query (just to test):
This works manually in MySQL:
"SELECT a0_.id AS id_0, a0_.name AS name_1, f1_.fin_size AS fin_size_2, d2_.tail_size AS tail_size_3, d2_.ears_size AS ears_size_4, a0_.discriminator AS discriminator_5 FROM animal a0_ LEFT JOIN fish f1_ ON a0_.id = f1_.id LEFT JOIN dog d2_ ON a0_.id = d2_.id WHERE d2_.tail_size = 12"

Here is an example code:

<?php
namespace CoreBundle\Entity;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="CoreBundle\Repository\AnimalRepository")
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn(name="discriminator", type="string")
 * @ORM\DiscriminatorMap({"animal":"CoreBundle\Entity\Animal","fish":"CoreBundle\Entity\Fish","dog":"CoreBundle\Entity\Dog"})
 */
class Animal
{

    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(type="string", nullable=true)
     */
    private $name;

}

<?php
namespace CoreBundle\Entity;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 */
class Dog extends \CoreBundle\Entity\Animal
{

    /**
     * @ORM\Column(type="integer", nullable=true)
     */
    private $tailSize;

    /**
     * @ORM\Column(type="integer", nullable=true)
     */
    private $earsSize;

}

<?php
namespace CoreBundle\Entity;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 */
class Fish extends \CoreBundle\Entity\Animal
{

    /**
     * @ORM\Column(type="integer", nullable=true)
     */
    private $finSize;

}

$em = $this->get('doctrine')->getManager();

$dogEntity = new Dog();
$dogEntity->setName('bulldog');
$dogEntity->setTailSize(12);
$dogEntity->setEarsSize(10);
$em->persist($dogEntity);

$fishEntity = new Fish();
$fishEntity->setName('trout');
$fishEntity->setFinSize(6);
$em->persist($fishEntity);

$animalEntity = new Animal();
$animalEntity->setName('test');
$em->persist($animalEntity);

$em->flush();

// OK
$animalRepository = $em->getRepository(Animal::class);
$queryBuilder = $animalRepository->createQueryBuilder('a');
$query = $queryBuilder->getQuery();
dump($query->getDQL());
dump($query->getSQL());
dump($query->getParameters());
$animals = $query->getArrayResult();
dump($animals);

// ERROR : Error: Class CoreBundle\\Entity\\Animal has no field or association named dog.tailSize
$animalRepository = $em->getRepository(Animal::class);
$queryBuilder = $animalRepository->createQueryBuilder('a');
$queryBuilder->where('a.dog.tailSize = 12'); // <<< ERROR <<<
// or : $queryBuilder->where('CoreBundle\Entity\Dog.tailSize = 12'); // <<< ERROR <<<
$query = $queryBuilder->getQuery();
dump($query->getDQL());
dump($query->getSQL());
dump($query->getParameters());
$animals = $query->getArrayResult();
dump($animals);

Yes .. it is possible to filter that the entity child:

$queryBuilder->where('a INSTANCE OF CoreBundle\Entity\Dog');

Which will add:
... WHERE a0_.discriminator IN ('dog')

It is still not possible to make a WHERE

[Ocramius]

From a type perspective, Animal.tailSize does not exist, while Dog.tailSize does.

DQL is not SQL, as DQL is statically checked. Support for this feature won't be added unless we add cast support in DQL.

Closing as invalid

[josedacosta]

It's no matter what! I understand better why not many people use the doctrine of inheritance: in fact, it is useless! Apart from doing a SELECT without WHERE. I do not see the value of inheritance if it's for not having access to the child's data filtering.
Thanks anyway :/

[Ocramius]

@josedacosta it is still useful if you select a single child in the inheritance.

[josedacosta]

yes I grant you, INSTANCE OF, has already helped me more than once
$queryBuilder->where('a INSTANCE OF CoreBundle\Entity\Dog');
thanks

[Ocramius]

No it needs to be the correct inheritance level in the FROM clause.

On 30 Mar 2017 1:41 p.m., "José DA COSTA" [e-mail] wrote:

yes I grant you, INSTANCE OF, has already helped me more than once
$queryBuilder->where('a INSTANCE OF CoreBundle\Entity\Dog');
thanks

—
You are receiving this because you were assigned.
Reply to this email directly, view it on GitHub
https://github.com/doctrine/doctrine2/issues/6371#issuecomment-290505883,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAJakCoYChmlAGk_aQ8zRGnVl248zhXjks5rq_c-gaJpZM4MtrYm
.

[patie]

i understand this not fit with doctrine idea, but its really missing feature

[Ocramius]

It's fully available in SQL ;-)

[renanBritz]

Hello @Ocramius!

Would that SQL be tightly coupled with MySQL for example?

If the parent class metadata also held the field mappings and association mappings of its child classes, would dql be able to "validate" the queries?

Best Regards!