Leave out discriminator part of Doctrine' generated SQL

doctrine-orm, dql, indexing, php, sql

Solution

Here is the solution I can think out. The idea is to extend some doctrine classes to add functionality required. Keep models as they are now.

Create new class extending SqlWalker (update namespaces of course)

<?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 making query 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_

Problem

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` clause from this query. On more complex queries this part of the `WHERE` clause makes it not possible to use some indexes that are defined. This can be resolved by adding `type` to the indexes, but this makes my indexes larger and I feel this is not necessary. The `AbstractPage` is the root in the inheritance tree. Thus we are interested in ALL records in the table. Omiting the `WHERE` part does precisely that. So the question is: how can I make Doctrine remove this `WHERE` part where it is not necessary. Thanks!

Original source