Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-719

Error in SQL subquery for a ManyToMany selfreferencing enitity when using the SIZE() or IS EMPTY dql function

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0-BETA2
    • Fix Version/s: 2.0-BETA3
    • Component/s: DQL, Mapping Drivers, ORM
    • Security Level: All
    • Labels:
      None
    • Environment:
      PHP 5.3, Ubuntu Lucid, MySQL 5, Apache 2.2

      Description

      I have an entity (Group) with a self-referencing ManyToMany association (parents, children).
      Now im just trying to query all groups without a parent.

      My DQL:

      SELECT g, c, d FROM Volkszaehler\Model\Group g LEFT JOIN g.children c LEFT JOIN g.channels d  WHERE g.parents IS EMPTY
      

      throws this PDOException:

      object(PDOException)#31 (8) {
        ["message":protected]=>
        string(89) "SQLSTATE[42S02]: Base table or view not found: 1146 Table 'volkszaehler.5_' doesn't exist"
        ["string":"Exception":private]=>
        string(0) ""
        ["code":protected]=>
        string(5) "42S02"
        ["file":protected]=>
        string(90) "/home/steffen/workspace/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php"
        ["line":protected]=>
        int(568)
        ["trace":"Exception":private]=>
        array(9) {
          [0]=>
          array(6) {
            ["file"]=>
            string(90) "/home/steffen/workspace/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php"
            ["line"]=>
            int(568)
            ["function"]=>
            string(5) "query"
            ["class"]=>
            string(3) "PDO"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(1) {
              [0]=>
              string(604) "SELECT g0_.name AS name0, g0_.description AS description1, g0_.id AS id2, g0_.uuid AS uuid3, g1_.name AS name4, g1_.description AS description5, g1_.id AS id6, g1_.uuid AS uuid7, c2_.name AS name8, c2_.description AS description9, c2_.indicator AS indicator10, c2_.resolution AS resolution11, c2_.cost AS cost12, c2_.id AS id13, c2_.uuid AS uuid14 FROM groups g0_ LEFT JOIN groups_groups g3_ ON g0_.id = g3_.parent_id LEFT JOIN groups g1_ ON g1_.id = g3_.child_id LEFT JOIN groups_channel g4_ ON g0_.id = g4_.group_id LEFT JOIN channels c2_ ON c2_.id = g4_.channel_id WHERE (SELECT COUNT(*) FROM  5_) = 0"
            }
          }
          [1]=>
          array(6) {
            ["file"]=>
            string(85) "/home/steffen/workspace/doctrine/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php"
            ["line"]=>
            int(46)
            ["function"]=>
            string(12) "executeQuery"
            ["class"]=>
            string(24) "Doctrine\DBAL\Connection"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(3) {
              [0]=>
              string(604) "SELECT g0_.name AS name0, g0_.description AS description1, g0_.id AS id2, g0_.uuid AS uuid3, g1_.name AS name4, g1_.description AS description5, g1_.id AS id6, g1_.uuid AS uuid7, c2_.name AS name8, c2_.description AS description9, c2_.indicator AS indicator10, c2_.resolution AS resolution11, c2_.cost AS cost12, c2_.id AS id13, c2_.uuid AS uuid14 FROM groups g0_ LEFT JOIN groups_groups g3_ ON g0_.id = g3_.parent_id LEFT JOIN groups g1_ ON g1_.id = g3_.child_id LEFT JOIN groups_channel g4_ ON g0_.id = g4_.group_id LEFT JOIN channels c2_ ON c2_.id = g4_.channel_id WHERE (SELECT COUNT(*) FROM  5_) = 0"
              [1]=>
              array(0) {
              }
              [2]=>
              array(0) {
              }
            }
          }
          [2]=>
          array(6) {
            ["file"]=>
            string(59) "/home/steffen/workspace/doctrine/lib/Doctrine/ORM/Query.php"
            ["line"]=>
            int(265)
            ["function"]=>
            string(7) "execute"
            ["class"]=>
            string(44) "Doctrine\ORM\Query\Exec\SingleSelectExecutor"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(3) {
              [0]=>
              object(Doctrine\DBAL\Connection)#16 (11) {
                ["_conn":protected]=>
                object(Doctrine\DBAL\Driver\PDOConnection)#29 (0) {
                }
                ["_config":protected]=>
                object(Doctrine\ORM\Configuration)#7 (1) {
                  ["_attributes":protected]=>
                  array(7) {
                    ["metadataCacheImpl"]=>
                    object(Doctrine\Common\Cache\ApcCache)#8 (2) {
                      ["_cacheIdsIndexId":"Doctrine\Common\Cache\AbstractCache":private]=>
                      string(18) "doctrine_cache_ids"
                      ["_namespace":"Doctrine\Common\Cache\AbstractCache":private]=>
                      NULL
                    }
                    ["queryCacheImpl"]=>
                    object(Doctrine\Common\Cache\ApcCache)#8 (2) {
                      ["_cacheIdsIndexId":"Doctrine\Common\Cache\AbstractCache":private]=>
                      string(18) "doctrine_cache_ids"
                      ["_namespace":"Doctrine\Common\Cache\AbstractCache":private]=>
                      NULL
                    }
                    ["metadataDriverImpl"]=>
                    object(Doctrine\ORM\Mapping\Driver\AnnotationDriver)#13 (4) {
                      ["_reader":"Doctrine\ORM\Mapping\Driver\AnnotationDriver":private]=>
                      object(Doctrine\Common\Annotations\AnnotationReader)#9 (2) {
                        ["parser":"Doctrine\Common\Annotations\AnnotationReader":private]=>
                        object(Doctrine\Common\Annotations\Parser)#10 (6) {
                          ["lexer":"Doctrine\Common\Annotations\Parser":private]=>
                          object(Doctrine\Common\Annotations\Lexer)#11 (5) {
                            ["tokens":"Doctrine\Common\Lexer":private]=>
                            array(0) {
                            }
                            ["position":"Doctrine\Common\Lexer":private]=>
                            int(0)
                            ["peek":"Doctrine\Common\Lexer":private]=>
                            int(0)
                            ["lookahead"]=>
                            NULL
                            ["token"]=>
                            NULL
                          }
                          ["isNestedAnnotation":"Doctrine\Common\Annotations\Parser":private]=>
                          bool(false)
                          ["defaultAnnotationNamespace":"Doctrine\Common\Annotations\Parser":private]=>
                          string(21) "Doctrine\ORM\Mapping\"
                          ["namespaceAliases":"Doctrine\Common\Annotations\Parser":private]=>
                          array(0) {
                          }
                          ["context":"Doctrine\Common\Annotations\Parser":private]=>
                          string(0) ""
                          ["autoloadAnnotations":"Doctrine\Common\Annotations\Parser":private]=>
                          bool(false)
                        }
                        ["cache":"Doctrine\Common\Annotations\AnnotationReader":private]=>
                        object(Doctrine\Common\Cache\ArrayCache)#12 (3) {
                          ["data":"Doctrine\Common\Cache\ArrayCache":private]=>
                          array(0) {
                          }
                          ["_cacheIdsIndexId":"Doctrine\Common\Cache\AbstractCache":private]=>
                          string(18) "doctrine_cache_ids"
                          ["_namespace":"Doctrine\Common\Cache\AbstractCache":private]=>
                          NULL
                        }
                      }
                      ["_paths":protected]=>
                      array(1) {
                        [0]=>
                        string(58) "/home/steffen/workspace/volkszaehler.org/backend/lib/Model"
                      }
                      ["_fileExtension":protected]=>
                      string(4) ".php"
                      ["_classNames":protected]=>
                      NULL
                    }
                    ["proxyDir"]=>
                    string(66) "/home/steffen/workspace/volkszaehler.org/backend/lib/Model/Proxies"
                    ["proxyNamespace"]=>
                    string(26) "Volkszaehler\Model\Proxies"
                    ["autoGenerateProxyClasses"]=>
                    bool(true)
                    ["sqlLogger"]=>
                    object(Volkszaehler\Util\Debug)#22 (5) {
                      ["queries":protected]=>
                      array(1) {
                        [0]=>
                        array(2) {
                          ["sql"]=>
                          string(604) "SELECT g0_.name AS name0, g0_.description AS description1, g0_.id AS id2, g0_.uuid AS uuid3, g1_.name AS name4, g1_.description AS description5, g1_.id AS id6, g1_.uuid AS uuid7, c2_.name AS name8, c2_.description AS description9, c2_.indicator AS indicator10, c2_.resolution AS resolution11, c2_.cost AS cost12, c2_.id AS id13, c2_.uuid AS uuid14 FROM groups g0_ LEFT JOIN groups_groups g3_ ON g0_.id = g3_.parent_id LEFT JOIN groups g1_ ON g1_.id = g3_.child_id LEFT JOIN groups_channel g4_ ON g0_.id = g4_.group_id LEFT JOIN channels c2_ ON c2_.id = g4_.channel_id WHERE (SELECT COUNT(*) FROM  5_) = 0"
                          ["parameters"]=>
                          array(0) {
                          }
                        }
                      }
                      ["messages":protected]=>
                      array(0) {
                      }
                      ["started":protected]=>
                      NULL
                      ["level":protected]=>
                      string(1) "1"
                      ["created"]=>
                      float(1280063214.6367)
                    }
                  }
                }
                ["_eventManager":protected]=>
                object(Doctrine\Common\EventManager)#14 (1) {
                  ["_listeners":"Doctrine\Common\EventManager":private]=>
                  array(0) {
                  }
                }
                ["_isConnected":"Doctrine\DBAL\Connection":private]=>
                bool(true)
                ["_transactionNestingLevel":"Doctrine\DBAL\Connection":private]=>
                int(0)
                ["_transactionIsolationLevel":"Doctrine\DBAL\Connection":private]=>
                int(2)
                ["_params":"Doctrine\DBAL\Connection":private]=>
                array(5) {
                  ["driver"]=>
                  string(9) "pdo_mysql"
                  ["host"]=>
                  string(9) "localhost"
                  ["user"]=>
                  string(2) "vz"
                  ["password"]=>
                  string(4) "demo"
                  ["dbname"]=>
                  string(12) "volkszaehler"
                }
                ["_platform":protected]=>
                object(Doctrine\DBAL\Platforms\MySqlPlatform)#17 (1) {
                  ["doctrineTypeMapping":protected]=>
                  NULL
                }
                ["_schemaManager":protected]=>
                NULL
                ["_driver":protected]=>
                object(Doctrine\DBAL\Driver\PDOMySql\Driver)#15 (0) {
                }
                ["_isRollbackOnly":"Doctrine\DBAL\Connection":private]=>
                bool(false)
              }
              [1]=>
              array(0) {
              }
              [2]=>
              array(0) {
              }
            }
          }
          [3]=>
          array(6) {
            ["file"]=>
            string(67) "/home/steffen/workspace/doctrine/lib/Doctrine/ORM/AbstractQuery.php"
            ["line"]=>
            int(522)
            ["function"]=>
            string(10) "_doExecute"
            ["class"]=>
            string(18) "Doctrine\ORM\Query"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(0) {
            }
          }
          [4]=>
          array(6) {
            ["file"]=>
            string(67) "/home/steffen/workspace/doctrine/lib/Doctrine/ORM/AbstractQuery.php"
            ["line"]=>
            int(360)
            ["function"]=>
            string(7) "execute"
            ["class"]=>
            string(26) "Doctrine\ORM\AbstractQuery"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(2) {
              [0]=>
              array(0) {
              }
              [1]=>
              int(1)
            }
          }
          [5]=>
          array(6) {
            ["file"]=>
            string(83) "/home/steffen/workspace/volkszaehler.org/backend/lib/Controller/GroupController.php"
            ["line"]=>
            int(57)
            ["function"]=>
            string(9) "getResult"
            ["class"]=>
            string(26) "Doctrine\ORM\AbstractQuery"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(0) {
            }
          }
          [6]=>
          array(6) {
            ["file"]=>
            string(78) "/home/steffen/workspace/volkszaehler.org/backend/lib/Controller/Controller.php"
            ["line"]=>
            int(54)
            ["function"]=>
            string(3) "get"
            ["class"]=>
            string(39) "Volkszaehler\Controller\GroupController"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(0) {
            }
          }
          [7]=>
          array(6) {
            ["file"]=>
            string(67) "/home/steffen/workspace/volkszaehler.org/backend/lib/Dispatcher.php"
            ["line"]=>
            int(149)
            ["function"]=>
            string(3) "run"
            ["class"]=>
            string(34) "Volkszaehler\Controller\Controller"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(1) {
              [0]=>
              string(3) "get"
            }
          }
          [8]=>
          array(6) {
            ["file"]=>
            string(58) "/home/steffen/workspace/volkszaehler.org/backend/index.php"
            ["line"]=>
            int(55)
            ["function"]=>
            string(3) "run"
            ["class"]=>
            string(23) "Volkszaehler\Dispatcher"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(0) {
            }
          }
        }
        ["previous":"Exception":private]=>
        NULL
        ["errorInfo"]=>
        array(3) {
          [0]=>
          string(5) "42S02"
          [1]=>
          int(1146)
          [2]=>
          string(37) "Table 'volkszaehler.5_' doesn't exist"
        }
      }
      

      Here is my Group Entitiy:

      namespace Volkszaehler\Model;
      
      use Doctrine\Common\Collections;
      
      use Doctrine\Common\Collections\ArrayCollection;
      
      /**
       * Group entity
       *
       * @author Steffen Vogel <info@steffenvogel.de>
       * @package default
       *
       * @Entity
       * @Table(name="groups")
       */
      class Group extends Entity {
      	/** @Column(type="string", nullable=false) */
      	protected $name;
      
      	/** @Column(type="string", nullable=true) */
      	protected $description;
      
      	/**
      	 * @ManyToMany(targetEntity="Channel", inversedBy="groups")
      	 * @JoinTable(name="groups_channel",
      	 * 		joinColumns={@JoinColumn(name="group_id", referencedColumnName="id")},
      	 * 		inverseJoinColumns={@JoinColumn(name="channel_id", referencedColumnName="id")}
      	 * )
      	 */
      	protected $channels = NULL;
      
      	/**
      	 * @ManyToMany(targetEntity="Group", inversedBy="parents")
      	 * @JoinTable(name="groups_groups",
      	 * 		joinColumns={@JoinColumn(name="parent_id", referencedColumnName="id")},
      	 * 		inverseJoinColumns={@JoinColumn(name="child_id", referencedColumnName="id")}
      	 * )
      	 */
      	protected $children = NULL;
      
      	/**
      	 * @ManyToMany(targetEntity="Group", mappedBy="children")
      	 */
      	protected $parents = NULL;
      
      	/**
      	 * construct
      	 */
      	public function __construct() {
      		parent::__construct();
      
      		$this->channels = new ArrayCollection();
      		$this->children = new ArrayCollection();
      		$this->parents = new ArrayCollection();
      	}
      
      	/**
      	 * adds group as new child
      	 *
      	 * @param Group $child
      	 * @todo check against endless recursion
      	 * @todo check if the group is already member of the group
      	 */
      	public function addGroup(Group $child) {
      		$this->children->add($child);
      	}
      
      	/**
      	 * adds channel as new child
      	 *
      	 * @param Channel $child
      	 * @todo check if the channel is already member of the group
      	 */
      	public function addChannel(Channel $child) {
      		$this->channels->add($child);
      	}
      
      	/**
      	 * getter & setter
      	 */
      	public function getName() { return $this->name; }
      	public function setName($name) { $this->name = $name; }
      	public function getDescription() { return $this->description; }
      	public function setDescription($description) { $this->description = $description; }
      	public function getChildren() { return $this->children; }
      	public function getParents() { return $this->parents; }
      	public function getChannels() { return $this->channels; }
      }
      

        Activity

        Steffen Vogel created issue -
        Roman S. Borschel made changes -
        Field Original Value New Value
        Fix Version/s 2.0-BETA4 [ 10072 ]
        Guilherme Blanco made changes -
        Assignee Roman S. Borschel [ romanb ] Guilherme Blanco [ guilhermeblanco ]
        Guilherme Blanco made changes -
        Attachment DDC719-patch.diff [ 10716 ]
        Guilherme Blanco made changes -
        Status Open [ 1 ] Closed [ 6 ]
        Resolution Fixed [ 1 ]
        Roman S. Borschel made changes -
        Resolution Fixed [ 1 ]
        Status Closed [ 6 ] Reopened [ 4 ]
        Roman S. Borschel made changes -
        Fix Version/s 2.0-BETA3 [ 10060 ]
        Fix Version/s 2.0-BETA4 [ 10072 ]
        Roman S. Borschel made changes -
        Status Reopened [ 4 ] Closed [ 6 ]
        Resolution Fixed [ 1 ]
        Benjamin Eberlei made changes -
        Workflow jira [ 11679 ] jira-feedback [ 15650 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 15650 ] jira-feedback2 [ 17514 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 17514 ] jira-feedback3 [ 19771 ]

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Steffen Vogel
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: