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 |        
Edit
Report