CODEWITHSUNDEEP
mysqldoctrine-orm
ACs
ACs

Reputation: 1445

How to LEFT JOIN a sub SELECT in doctrine

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.

Upvotes: 0

Views: 8628

Answers (3)

Ahmed Amin Shahin
Ahmed Amin Shahin

Reputation: 1143

$sql = "
    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 calculation c
    INNER JOIN calculation_item i ON c.id = i.orderId
    LEFT JOIN user u ON c.openedBy = u.id
    LEFT JOIN user cu ON c.customerDmsId = cu.user
    LEFT JOIN (
        SELECT DISTINCT TRIM(cp.originalteilenummer) AS productCode
        FROM external_product cp
    ) cp ON i.partNumber = cp.productCode
    GROUP BY c.id
";

$result = self::$entityManager->getConnection()->executeQuery($sql)->fetchAllAssociative();

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64486

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().')' ))

Upvotes: 0

Igor W.
Igor W.

Reputation: 431

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

Upvotes: 0

Related Questions