Skip to content

Loading…

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

Closed
doctrinebot opened this Issue · 6 comments

2 participants

@doctrinebot

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), ',');
@doctrinebot

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

Comment created by tbo:

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

@doctrinebot

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

Comment created by @beberlei:

Fixed in DDC-117 branch.

@doctrinebot

Issue was closed with resolution "Fixed"

@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.1 milestone
@doctrinebot doctrinebot closed this
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.