How to LEFT JOIN a sub SELECT in doctrine

Question

I don't want to do any special magic, just LEFT JOIN my query to a sub query. I've tried many ways and tricks found on the internet, but no one works, and I always got useless error messages, that tells noting meaningful about the problem or gives no point to search for solutions.

Here is my subquery and query:

$subQuery = $qb
            ->select("DISTINCT TRIM(cp.originalteilenummer) AS productCode")
            ->from(\Vendor\ShopBundle\Entity\ExternalProduct::class, 'cp')
            ->getQuery();


$result = self::$entityManager->createQueryBuilder()
            ->select('c.id,
                      c.manufacturerId,
                      cu.fullName,
                      c.vin,
                      c.plateNumber,
                      c.netDiscountPrice,
                      c.calculationDate,
                      u.loginName,
                      c.lastOfferSentAt,
                      COUNT(DISTINCT i.id) AS items,
                      c.customerDmsId,
                      GROUP_CONCAT(cp.productCode) AS productCodes')
            ->from(\Vendor\ShopBundle\Entity\Calculation::class, 'c')
            ->innerJoin(\Vendor\ShopBundle\Entity\CalculationItem::class, 'i', 'WITH', 'c.id = i.orderId')
            ->leftJoin(\Vendor\UserBundle\Entity\User::class, 'u', 'WITH', 'c.openedBy = u.id')
            ->leftJoin(\Vendor\CoreBundle\Entity\User::class, 'cu', 'WITH', 'c.customerDmsId = cu.user')
            ->leftJoin(sprintf('(%s)', $subQuery->getSQL()), 'cp', 'WITH', 'i.partNumber = cp.productCode')
            ->groupBy('c.id')
            ->getQuery()
            ->getScalarResult();

I just want to left join my query to the dataset of the subquery. How could I achieve this?

If I run this, I get an error:

[Semantical Error] line 0, col 773 near '(SELECT DISTINCT': Error: Class '(' is not defined.

Take a moment and learn the join syntax for DQL (not SQL). Hint: leftJoin(c.openBy','u') is all you need. Get the basics working then focus on your subquery. — Cerad, Aug 16 '17 at 12:32
@Cerad Without the sub query the basic query works properly. As you said leftJoin(c.openBy','u') would be enough, but only if there is an association between the 'c' and 'u', but in this case there is not. With the syntax I used one can join two 'unrelated' entities by the given condition. So the problem is still that doctrine definitely expects an entity class at the point where I'd like to pass the sub query instead. — ACs, Aug 16 '17 at 13:08

score 0 · Answer 1 · answered Aug 17 '17 at 08:13

Thing you are trying to do could be impossible to achieve with QB and Doctrine.

Better approach would be use subquery in WITH IN/NOT IN. But it could be not what you want get.

Sources:

How to create LEFT JOIN with SELECT subquery using QueryBuilder in Doctrine 2?

Doing a WHERE .. IN subquery in Doctrine 2

score 0 · Answer 2 · answered Sep 16 '17 at 13:38

This should work, you are trying to put getSQL() instead use getDQL()

->leftJoin(sprintf('(%s)', $subQuery->getSQL()), 'cp', 'WITH', 'i.partNumber = cp.productCode')

To

->leftJoin('VendorShopBundle:ExternalProduct', 'cp', 'WITH', $qb->expr()->eq( 'i.partNumber', '('.$subQuery->getDQL().')' ))

How to LEFT JOIN a sub SELECT in doctrine

2 Answers2