Skip to content

Loading…

DDC-649: SQL Error with single table inheritance and findAll method #5158

Closed
doctrinebot opened this Issue · 10 comments

2 participants

@doctrinebot

Jira issue originally created by user needle:

When selecting all objects of SecondClass and ThirdClass which both inherit from FirstClass with the following code :

$em->getRepository('FirstClass')->findAll();

Postgresql is throwing the following error :
Invalid text representation: 7 ERROR: invalid input syntax for integer: "" LINE 1: ...erstuff3 FROM "firstclass" t0 WHERE t0.type IN ('', '1', '...

I think it's because of the empty string after the IN clause.

There is the fail test case :

Classes :

class FirstClass {

  private $id;
  private $type;
}

class SecondClass extends FirstClass {
  private $otherStuff;
}

class ThirdClass extends FirstClass {
  private $otherStuff;
}

Mapping :

<?xml version="1.0" encoding="UTF-8"?>
  <doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd">

  <entity name="FirstClass" table='"first*class"' inheritance-type="SINGLE*TABLE">
    <id name="id" type="integer" column="id">
      <generator strategy="SEQUENCE"/>
      <sequence-generator sequence-name="first*class_id*seq" allocation-size="1" initial-value="1"/>
    </id>

    <discriminator-column name="type" type="integer" field-name="type" />

    <discriminator-map>
      <discriminator-mapping value="1" class="ThirdClass" />
      <discriminator-mapping value="2" class="SecondClass" />
    </discriminator-map>
  </entity>
  </doctrine-mapping>

<?xml version="1.0" encoding="UTF-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd">

  <entity name="SecondClass">
    <field name="otherStuff" column="other_stuff" type="string" />
  </entity>
</doctrine-mapping>

<?xml version="1.0" encoding="UTF-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd">

  <entity name="ThirdClass">
    <field name="otherStuff" column="other_stuff" type="string" />
  </entity>
</doctrine-mapping>

The SQL :

CREATE TABLE first_class
(
  id serial NOT NULL,
  "type" integer,
  other_stuff character varying,
  CONSTRAINT first*class*pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

The fail test :

require_once 'class/FirstClass.php';
require_once 'class/SecondClass.php';
require_once 'class/ThirdClass.php';

$entities = $doctrineEntityManager->getRepository('FirstClass')->findAll();
@doctrinebot

Comment created by needle:

The problem seems to come from Doctrine\ORM\Persisters\SingleTablePersister::_getSelectConditionSQL()

@doctrinebot

Comment created by bmills:

I am also experiencing this exact issue. The value of the empty string in the 'IN' clause would appear to be the 'discriminatorValue' field on the instance of ClassMetadataInfo that gets passed into SingleTablePersister. For me, that value is null.

As a side note, this came up when I was trying to switch all my entities from using Annotation to XML. When using Annotations, I do not experience the problem with this query, which leads me to believe the issues lies somewhere in the XML drivers.

That's about as far as I've gotten trying to debug it.

@doctrinebot

Comment created by needle:

Why do not juste change the _getSelectConditionSQL() to the following :

protected function _getSelectConditionSQL(array $criteria, $assoc = null)
    {
        $conditionSql = parent::_getSelectConditionSQL($criteria, $assoc);

        // Append discriminator condition
        if ($conditionSql) $conditionSql .= ' AND ';
        if (isset($this->_class->discriminatorValue)) {
            $values = array($this->*conn->quote($this->*class->discriminatorValue));
        }
        $discrValues = array*flip($this->*class->discriminatorMap);
        foreach ($this->_class->subClasses as $subclassName) {
            $values[] = $this->_conn->quote($discrValues[$subclassName]);
        }
        $conditionSql .= $this->*getSQLTableAlias($this->*class->name) . '.'
                . $this->_class->discriminatorColumn['name']
                . ' IN (' . implode(', ', $values) . ')';

        return $conditionSql;
    }
@doctrinebot

Comment created by @beberlei:

There is a fundamental flaw in your logic i believe.

The parent entity has to be part of the entity map, otherwise it cannot be used as a starting point for findAll() on the repository.

   <discriminator-map>
      <discriminator-mapping value="0" class="FirstClass" />
      <discriminator-mapping value="1" class="ThirdClass" />
      <discriminator-mapping value="2" class="SecondClass" />
    </discriminator-map>
@doctrinebot

Comment created by needle:

But what if the FirstClass is abstract ?
Then it can't be part of the entity map, isn't it ?

However I should be able to select all its children classes. Anyway I think Doctrine should allow me to do it ;)

@doctrinebot

Comment created by @beberlei:

even if it is abstract it is part of the hierachy and can be selected in DQL, repository and find methods. Since you cannot instantiate
the abstract class in userland anyways there is never going to be a problem. It still has to be done.

Maybe we should throw an exception in the mapping drivers if this is not done properly

@doctrinebot

Comment created by needle:

Correct me if I'm wrong, but if I do such a thing then i'll have a discriminator value wich will never be used.
It seems strange to me but as long as it works :)

Thank you for the explanation.

@doctrinebot

Comment created by @beberlei:

add it for now, you are right its never used. We will look into a change.

@doctrinebot

Comment created by @beberlei:

Fixed in master

@doctrinebot

Issue was closed with resolution "Fixed"

@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.0-BETA3 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.