DDC-795: Wrong SQL statement when using loadOneToManyCollection #977

New Issue

2026-01-22T12:57:54+01:00

admin commented

2026-01-22 12:57:54 +01:00

Originally created by @doctrinebot on GitHub (Sep 9, 2010).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user tbo:

I have the following SQL


CREATE TABLE `article` (
  `articleID` int(11) NOT NULL AUTO_INCREMENT,
  `reference` varchar(255) DEFAULT NULL,
  `addDt` datetime DEFAULT NULL,
  PRIMARY KEY (`articleID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `articleRelated` (
  `articleID` int(11) NOT NULL,
  `relatedArticleID` int(11) NOT NULL,
  PRIMARY KEY (`articleID`,`relatedArticleID`),
  KEY `a1` (`articleID`),
  KEY `a2` (`relatedArticleID`),
  CONSTRAINT `a1` FOREIGN KEY (`articleID`) REFERENCES `article` (`articleID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `a2` FOREIGN KEY (`relatedArticleID`) REFERENCES `article` (`articleID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And these 2 models

<?php
/****
 * Model for article
 * 
 * @Table(name="article") 
 * @Entity
 */
class App*Model*Article
{

    /****
     * @var integer articleID
     * @Column(name="articleID", type="integer", nullable=false) 
     * @GeneratedValue(strategy="IDENTITY") 
     * @Id
     */
    protected $_articleID = null;

    /****
     * @var string reference
     * @Column(name="reference", type="string", nullable=true, length=255)
     */
    protected $_reference = null;

    /****
     * @var datetime addDt
     * @Column(name="addDt", type="datetime", nullable=true)
     */
    protected $_addDt = null;

    /****
     * @OneToMany(targetEntity="App*Model_ArticleRelated", mappedBy="*article", cascade={"persist"})
     */
    protected $_articleRelatedRefArticle = array();

    /****
     * @OneToMany(targetEntity="App*Model_ArticleRelated", mappedBy="*relatedArticle", cascade={"persist"})
     */
    protected $_articleRelatedRefRelatedArticle = array();
}

and

<?php

/****
 * Model for articleRelated
 * 
 * @Table(name="articleRelated") 
 * @Entity
 */
class App*Model*ArticleRelated
{

    /****
     * @ManyToOne(targetEntity="App*Model*Article") 
     * @JoinColumn(name="articleID", referencedColumnName="articleID") 
     * @Id
     */
    protected $_article = null;

    /****
     * @ManyToOne(targetEntity="App*Model*Article") 
     * @JoinColumn(name="relatedArticleID", referencedColumnName="articleID") 
     * @Id
     */
    protected $_relatedArticle = null;
}

When I do the following

$firstArticle = $this->*entityManager->find('App_Model*Article', 54);

$related = $firstArticle->getArticleRelated('article');
foreach ($related as $art) {
        var_dump($art);
}

it generates the following SQL

SELECT , t1.articleID AS articleID0, t1.relatedArticleID AS relatedArticleID2 FROM articleRelated t1 WHERE articleID = ?

I tracked the problem down to the following method

//...
protected function _getSelectColumnListSQL()
    {
        if ($this->_selectColumnListSql !== null) {
            return $this->_selectColumnListSql;
        }

        $columnList = '';

        // Add regular columns to select list
        foreach ($this->_class->fieldNames as $field) {
            if ($columnList) $columnList .= ', ';
            $columnList .= $this->*getSelectColumnSQL($field, $this->*class);
        }

        $this->*selectColumnListSql = $columnList . $this->_getSelectJoinColumnsSQL($this->*class);

        return $this->_selectColumnListSql;
    }
//....

Because $this->_class->fieldNames is empty for my class, $columnList will also be empty.
$this->_getSelectJoinColumnsSQL($this->_class) generates
", t1.articleID AS articleID0, t1.relatedArticleID AS relatedArticleID2"
so $this->_selectColumnListSql == ", t1.articleID AS articleID0, t1.relatedArticleID AS relatedArticleID2"

I fixed the problem by adding trim:

$this->*selectColumnListSql = trim($columnList . $this->_getSelectJoinColumnsSQL($this->*class), ',');

Originally created by @doctrinebot on GitHub (Sep 9, 2010). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user tbo: I have the following SQL ``` CREATE TABLE `article` ( `articleID` int(11) NOT NULL AUTO_INCREMENT, `reference` varchar(255) DEFAULT NULL, `addDt` datetime DEFAULT NULL, PRIMARY KEY (`articleID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `articleRelated` ( `articleID` int(11) NOT NULL, `relatedArticleID` int(11) NOT NULL, PRIMARY KEY (`articleID`,`relatedArticleID`), KEY `a1` (`articleID`), KEY `a2` (`relatedArticleID`), CONSTRAINT `a1` FOREIGN KEY (`articleID`) REFERENCES `article` (`articleID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `a2` FOREIGN KEY (`relatedArticleID`) REFERENCES `article` (`articleID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ``` And these 2 models ``` <?php /**** * Model for article * * @Table(name="article") * @Entity */ class App*Model*Article { /**** * @var integer articleID * @Column(name="articleID", type="integer", nullable=false) * @GeneratedValue(strategy="IDENTITY") * @Id */ protected $_articleID = null; /**** * @var string reference * @Column(name="reference", type="string", nullable=true, length=255) */ protected $_reference = null; /**** * @var datetime addDt * @Column(name="addDt", type="datetime", nullable=true) */ protected $_addDt = null; /**** * @OneToMany(targetEntity="App*Model_ArticleRelated", mappedBy="*article", cascade={"persist"}) */ protected $_articleRelatedRefArticle = array(); /**** * @OneToMany(targetEntity="App*Model_ArticleRelated", mappedBy="*relatedArticle", cascade={"persist"}) */ protected $_articleRelatedRefRelatedArticle = array(); } ``` and ``` <?php /**** * Model for articleRelated * * @Table(name="articleRelated") * @Entity */ class App*Model*ArticleRelated { /**** * @ManyToOne(targetEntity="App*Model*Article") * @JoinColumn(name="articleID", referencedColumnName="articleID") * @Id */ protected $_article = null; /**** * @ManyToOne(targetEntity="App*Model*Article") * @JoinColumn(name="relatedArticleID", referencedColumnName="articleID") * @Id */ protected $_relatedArticle = null; } ``` When I do the following ``` $firstArticle = $this->*entityManager->find('App_Model*Article', 54); $related = $firstArticle->getArticleRelated('article'); foreach ($related as $art) { var_dump($art); } ``` it generates the following SQL ``` SELECT , t1.articleID AS articleID0, t1.relatedArticleID AS relatedArticleID2 FROM articleRelated t1 WHERE articleID = ? ``` I tracked the problem down to the following method ``` //... protected function _getSelectColumnListSQL() { if ($this->_selectColumnListSql !== null) { return $this->_selectColumnListSql; } $columnList = ''; // Add regular columns to select list foreach ($this->_class->fieldNames as $field) { if ($columnList) $columnList .= ', '; $columnList .= $this->*getSelectColumnSQL($field, $this->*class); } $this->*selectColumnListSql = $columnList . $this->_getSelectJoinColumnsSQL($this->*class); return $this->_selectColumnListSql; } //.... ``` Because $this->_class->fieldNames is empty for my class, $columnList will also be empty. $this->_getSelectJoinColumnsSQL($this->_class) generates ", t1.articleID AS articleID0, t1.relatedArticleID AS relatedArticleID2" so $this->_selectColumnListSql == ", t1.articleID AS articleID0, t1.relatedArticleID AS relatedArticleID2" I fixed the problem by adding trim: ``` $this->*selectColumnListSql = trim($columnList . $this->_getSelectJoinColumnsSQL($this->*class), ','); ```

admin closed this issue

2026-01-22 12:57:56 +01:00

admin commented

2026-01-22 12:57:56 +01:00

@doctrinebot commented on GitHub (Sep 9, 2010):

duplicates DDC-117: Allow @Id on @ManyToOne fields

@doctrinebot commented on GitHub (Sep 9, 2010): - duplicates [DDC-117: Allow @Id on @ManyToOne fields](http://www.doctrine-project.org/jira/browse/DDC-117)

admin commented

2026-01-22 12:57:58 +01:00

@doctrinebot commented on GitHub (Sep 10, 2010):

Comment created by @beberlei:

Foreign Keys as Primary Keys are currently not supported (except for the DDC-117 experimental branch, scheduled for 2.1)

See: http://www.doctrine-project.org/projects/orm/2.0/docs/reference/limitations-and-known-issues/en#current-limitations:foreign-keys-as-identifiers

@doctrinebot commented on GitHub (Sep 10, 2010): Comment created by @beberlei: Foreign Keys as Primary Keys are currently not supported (except for the [DDC-117](http://www.doctrine-project.org/jira/browse/DDC-117) experimental branch, scheduled for 2.1) See: http://www.doctrine-project.org/projects/orm/2.0/docs/reference/limitations-and-known-issues/en#current-limitations:foreign-keys-as-identifiers

admin commented

2026-01-22 12:57:58 +01:00

@doctrinebot commented on GitHub (Sep 10, 2010):

Comment created by tbo:

Sorry Benjamin, I should have mentioned that I work with that branch.

@doctrinebot commented on GitHub (Sep 10, 2010): Comment created by tbo: Sorry Benjamin, I should have mentioned that I work with that branch.

admin commented

2026-01-22 12:58:00 +01:00

@doctrinebot commented on GitHub (Sep 12, 2010):

Comment created by @beberlei:

Ah ok, i'll move and downgrade priority because there is so much to do on the master branch before release.

@doctrinebot commented on GitHub (Sep 12, 2010): Comment created by @beberlei: Ah ok, i'll move and downgrade priority because there is so much to do on the master branch before release.

admin commented

2026-01-22 12:58:00 +01:00

@doctrinebot commented on GitHub (Dec 28, 2010):

Comment created by @beberlei:

Fixed in DDC-117 branch.

@doctrinebot commented on GitHub (Dec 28, 2010): Comment created by @beberlei: Fixed in [DDC-117](http://www.doctrine-project.org/jira/browse/DDC-117) branch.

admin commented

2026-01-22 12:58:02 +01:00