Doctrine 2 ManyToOne with multiple joinColumns

Question

I am trying to select the appropriate row in the product_item_sortorder table based on productId and toolboxItemId from the product_item table.

In plain SQL, which would be for a given productId:

SELECT pi.*, pis.* FROM product_item pi LEFT JOIN product_item_sortorder pis ON pi.productId = pis.productId AND pi.toolboxItemId = pis.toolboxItemId WHERE pi.productId = 6 

I wrote a DQL for it:

 $this->_em->createQuery( 'SELECT pi FROM Entities\ProductItem pi LEFT JOIN pi.sequence s WHERE pi.product = ?1' ); 

Then I get the following SQL if I output $ query-> getSQL ():

 SELECT p0_.id AS id0, p0_.productId AS productId1, p0_.priceGroupId AS priceGroupId2, p0_.toolboxItemId AS toolboxItemId3, p0_.levelId AS levelId4, p0_.parentId AS parentId5, p0_.productId AS productId6, p0_.toolboxItemId AS toolboxItemId7 FROM product_item p0_ LEFT JOIN product_item_sortorder p1_ ON p0_.productId = p1_. AND p0_.toolboxItemId = p1_. WHERE p0_.productId = ? ORDER BY p0_.id ASC 

As you can see, no column reference names were found:

 LEFT JOIN product_item_sortorder p1_ ON p0_.productId = p1_. AND p0_.toolboxItemId = p1_. 

Details of the product_item table:

 +-----+-----------+---------------+ | id | productId | toolboxItemId | +-----+-----------+---------------+ | 467 | 1 | 3 | | 468 | 1 | 10 | | 469 | 1 | 20 | | 470 | 1 | 4 | | 471 | 1 | 10 | +-----+-----------+---------------+ 

Details of the product_item_sortorder table:

 +-----+-----------+---------------+----------+ | id | productId | toolboxItemId | sequence | +-----+-----------+---------------+----------+ | 452 | 1 | 3 | 1 | | 457 | 1 | 4 | 6 | | 474 | 1 | 20 | 4 | +-----+-----------+---------------+----------+ 

ProductItem Entity

 <?php /** * @Entity(repositoryClass="Repositories\ProductItem") * @Table(name="product_item") */ class ProductItem { ... /** * @ManyToOne(targetEntity="ProductItemSortorder") * @JoinColumns({ * @JoinColumn(name="productId", referencedColumnName="productId"), * @JoinColumn(name="toolboxItemId", referencedColumnName="toolboxItemId") * }) */ protected $sequence; ... ?> 

ProductItemSortOrder Entity

 <?php /** * @Entity(repositoryClass="Repositories\ProductItemSortorder") * @Table(name="product_item_sortorder") */ class ProductItemSortorder { ... /** * @ManyToOne(targetEntity="Product") * @JoinColumn(name="productId", referencedColumnName="id") */ protected $product; /** * @ManyToOne(targetEntity="ToolboxItem") * @JoinColumn(name="toolboxItemId", referencedColumnName="id") */ protected $toolboxItem; ... } ?> 

Answer 1

Check this example for a combo box column https://github.com/doctrine/doctrine2/blob/2.0.6/tests/Doctrine/Tests/Models/Navigation/NavPhotos.php

Answer 2

Your comparisons are serious errors. You use ManyToOne from both ends, how is this possible? You have both associations defined as “owning” - third-party, without matching or referring (see Section “Associating Associations”). And you use the join columns of one association to map many fields in another object. I suppose you want to do something else, can you accurately describe your use case?

Answer 3

How could you compare your example in YAML (since the @Hernan Rajchert example is presented only in annotations):

 ProductItem: type: entity manyToOne: sequence: targetEntity: ProductItemSortorder joinColumns: productId: referencedColumnName: productId toolboxItemId: referencedColumnName: toolboxItemId