Doctrine 2 - ORM

SQL Error with single table inheritance and findAll method

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Critical Critical
  • Resolution: Fixed
  • Affects Version/s: 2.0-BETA1, 2.0-BETA2
  • Fix Version/s: 2.0-BETA3
  • Component/s: ORM
  • Security Level: All
  • Labels:
    None
  • Environment:
    Postgresql

Description

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: ...er_stuff3 FROM "first_class" 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();

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Paul Fariello added a comment -

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

Show
Paul Fariello added a comment - The problem seems to come from Doctrine\ORM\Persisters\SingleTablePersister::_getSelectConditionSQL()
Hide
Permalink
Bryan Mills added a comment -

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.

Show
Bryan Mills added a comment - 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.
Hide
Permalink
Paul Fariello added a comment -

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;
    }
Show
Paul Fariello added a comment - 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; }
Hide
Permalink
Benjamin Eberlei added a comment -

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>
Show
Benjamin Eberlei added a comment - 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>
Hide
Permalink
Paul Fariello added a comment -

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

Show
Paul Fariello added a comment - 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
Hide
Permalink
Benjamin Eberlei added a comment -

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

Show
Benjamin Eberlei added a comment - 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
Hide
Permalink
Paul Fariello added a comment -

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.

Show
Paul Fariello added a comment - 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.
Hide
Permalink
Benjamin Eberlei added a comment -

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

Show
Benjamin Eberlei added a comment - add it for now, you are right its never used. We will look into a change.
Hide
Permalink
Benjamin Eberlei added a comment -

Fixed in master

Show
Benjamin Eberlei added a comment - Fixed in master

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Paul Fariello
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: