Leave the discriminant part of the generated Doctrine SQL

Question

Assume the following AbstractPage model:

 /* * @ORM\Entity * @ORM\Table(name="page") * @ORM\InheritanceType("SINGLE_TABLE") * @ORM\DiscriminatorColumn(name="type", type="string") * @ORM\DiscriminatorMap * ({ * "page" = "Page", * "link" = "Link" * }) */ 

And the following DQL query:

 SELECT p FROM \Page\Model\AbstractPage 

The generated SQL will be:

 SELECT ... FROM page p0_ WHERE p0_.type IN ('page', 'link') 

Now to the question: how can I remove the WHERE from this query. In more complex queries, this part of the WHERE does not allow the use of specific indexes that are defined. This can be resolved by adding type to the indexes, but it makes my indexes large, and I think this is optional.

AbstractPage is the root in the inheritance tree. Thus, we are interested in ALL entries in the table. Lowering the WHERE part does just that.

So the question is: how can I get Doctrine to remove this part of WHERE where it is not needed.

Thanks!

Answer 1

Here is a solution I can come up with. The idea is to expand some doctrine classes to add the necessary functionality. Keep the models as they are now.

Create a new class extending SqlWalker (of course, update namespaces)

 <?php namespace Sad; use Doctrine\ORM\Mapping\ClassMetadata; use Doctrine\ORM\Query\SqlWalker; class CustomSqlWalker extends SqlWalker { const IGNORE_DISCRIMINATION = 'ignoreDiscrimination'; /** * {@inheritdoc} */ public function walkWhereClause($whereClause) { // Get list of aliases in which discrimination should be ignored $ignoreDescription = $this->getQuery()->getHint(self::IGNORE_DISCRIMINATION); if ($ignoreDescription !== false) { // For each aliases... foreach ($this->getQueryComponents() as $k => $component) { // ...check if alias is in ignore list if (in_array($k, $ignoreDescription)) { /** @var $metaObj ClassMetadata */ $metaObj = $component['metadata']; // Update inheritance type to disable discrimination where if ($metaObj->isInheritanceTypeSingleTable()) { $metaObj->setInheritanceType(ClassMetadata::INHERITANCE_TYPE_NONE); } } } } return parent::walkWhereClause($whereClause); } } 

Then, by executing the request, you do the following:

 echo $entityManager->createQuery("SELECT p FROM \Sad\Schema\AbstractPage as p")->getSQL(); // Output: ELECT p0_.id AS id_0, p0_.name AS name_1, p0_.type AS type_2 FROM page p0_ WHERE p0_.type IN ('page', 'link') $query = $entityManager->createQuery("SELECT p FROM \Sad\Schema\AbstractPage as p"); // Make sure query uses custom walker $query->setHint(\Doctrine\ORM\Query::HINT_CUSTOM_OUTPUT_WALKER, 'Sad\CustomSqlWalker'); // Specify which aliases should ignore discrimination $query->setHint(\Sad\CustomSqlWalker::IGNORE_DISCRIMINATION, array('p')); echo $query->getSQL(); // Output: ELECT p0_.id AS id_0, p0_.name AS name_1, p0_.type AS type_2 FROM page p0_