Doctrine 2 ManyToOne with multiple joinColumns
Solution 1
Your mappings are seriously wrong. You are using ManyToOne on both ends, how is this possible? You have both associations defined as "owning"-side, no mapped-by or inversed-by (See Association Mappings chapter). And you are using join columns of one association to map to many fields in another entity. I suppose you want to do something else, can you describe exactly your use-case?
Solution 2
How you would map your example in YAML (since @Hernan Rajchert's example is only in annotations):
ProductItem:
type: entity
manyToOne:
sequence:
targetEntity: ProductItemSortorder
joinColumns:
productId:
referencedColumnName: productId
toolboxItemId:
referencedColumnName: toolboxItemId
Related videos on Youtube
tom
I'm a PHP consultant at PHPro. I have a blog on tombevers.me. With broad interests in PHP, Symfony, frontend development (XHTML, CSS), webdesign, usability, Ruby, Ruby On Rails, jQuery, Git, ...
Updated on November 26, 2020Comments
-
tom over 3 years
I'm trying to select the matching row in the product_item_sortorder table based on a productId and toolboxItemId from the product_item table.
In normal SQL that 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 the DQL for it as followed:
$this->_em->createQuery( 'SELECT pi FROM Entities\ProductItem pi LEFT JOIN pi.sequence s WHERE pi.product = ?1' );
Then I get following SQL if I output the $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 the referencedColumnNames are not 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; ... } ?>
-
tom about 13 yearsI updated the question a little bit and below you can see which query I want to execute