Allow @Id on @ManyToOne fields (DDC-117)

[DDC-795] Wrong SQL statement when using loadOneToManyCollection Created: 09/Sep/10  Updated: 28/Dec/10  Resolved: 28/Dec/10

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.0-BETA4
Fix Version/s: 2.1
Security Level: All

Type: Sub-task Priority: Minor
Reporter: Van Hauwaert Bert Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates DDC-117 Allow @Id on @ManyToOne fields Resolved

 Description   

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

Article.php
<?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

ArticleRelated.php
<?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

Doctrine/ORM/Persisters/BasicEntityPersister.php
//...
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), ',');


 Comments   
Comment by Benjamin Eberlei [ 10/Sep/10 ]

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

Comment by Van Hauwaert Bert [ 10/Sep/10 ]

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

Comment by Benjamin Eberlei [ 12/Sep/10 ]

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

Comment by Benjamin Eberlei [ 28/Dec/10 ]

Fixed in DDC-117 branch.

Generated at Fri Oct 31 18:47:10 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.