Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-649

SQL Error with single table inheritance and findAll method

    Details

    • Type: Bug Bug
    • Status: 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

        Hide
        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
        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
        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
        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
        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
        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
        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
        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
        Benjamin Eberlei added a comment -

        Fixed in master

        Show
        Benjamin Eberlei added a comment - Fixed in master

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Paul Fariello
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: