Symfony3/Doctrine2 : subquery with InnerJoin using QueryBuilder

Question

In a catalog, I have products and articles. Articles are variants of products. In a catalog, products are sorted in categories and a product can be one or many times in a catalog.

I'd like to get articles of a catalog, but my articles aren't assigned directly to the catalog, only products are.

I'd like to construct the following SQL using Doctrine's query builder:

SELECT a.code, a.productCode, a.name
FROM Article a
INNER JOIN (
    SELECT p.code
    FROM Product p
    WHERE p.catalogCode = 'MYCODE'
    GROUP BY p.code
    ORDER BY p.code ASC
) AS results ON results.productCode = a.productCode

This query works in MySQL. I tried to do it in the Repository of my entity, but I have an error :

public function findArticlesByCatalog($catatlogCode)
{
   return $this->getEntityManager()
        ->createQuery(
            'SELECT a.code, a.productCode, a.name
                FROM AppBundle:Article a
                INNER JOIN (
                    SELECT p.code
                    FROM AppBundle:CatalogProduct p
                    WHERE p.catalogCode = :code
                    GROUP BY p.code
                    ORDER BY p.code ASC
                ) AS results ON results.productCode = a.productCode'
        )
        ->setParameter('code', $catatlogCode)
        ->getResult();
}

Error (just after INNER JOIN) :

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

So, I'd like to construct it using Doctrine's query builder in my Controller.

I started something but I don't know to finish it...

$repository = $em->getRepository('AppBundle:Article');

$qb = $repository->createQueryBuilder('a');
$qb->select(array('a.code', 'a.productCode', 'a.name'))
    ->innerJoin(
        'AppBundle:CatalogProduct', 'p',
        'WITH',
        $qb->select('p.code')
           ->where(
              $qb->expr()->eq('p.catalogCode', ':code')
           )
           ->setParameter('code', $catCode)
           ->groupBy('p.code')
           ->orderBy('p.code', 'ASC')
    )
// ...

How to specify the rest of the query ?

AS results ON results.productCode = a.productCode'

Thanks for your help !

Answer 1

I found the right way to do this.

I rewrote my SQL to do the same that my first one in order to do it easily with query builder.

So, my first SQL :

SELECT a.code, a.productCode, a.name
FROM Article a
INNER JOIN (
    SELECT p.code
    FROM Product p
    WHERE p.catalogCode = 'MYCODE'
    GROUP BY p.code
    ORDER BY p.code ASC
) AS cat_art ON cat_art.productCode = a.productCode

...has the same results that this one :

SELECT DISTINCT a.code, a.productCode, a.name
FROM Article a
JOIN Product p ON a.productCode = p.code
WHERE p.code IN (
    SELECT p.code
    FROM Product p
    WHERE p.catalogCode = 'MYCODE'
    GROUP BY p.code
    ORDER BY p.code ASC
)

With query builder, we should write 2 queries with 2 different query builders :

# It is very important here to name it "p2", not "p" because,
# in the main query, there is already "p"
$qb2 = $em->getRepository('AppBundle:CatalogProduct')->createQueryBuilder('p2');

$subQuery = $qb2->select('p2.code')
    ->where(
        $qb2->expr()->eq('p2.catalogCode', ':code')
    )
    ->groupBy('p2.code')
    ->orderBy('p2.code', 'ASC');

# main query
$qb = $em->getRepository('AppBundle:Article')->createQueryBuilder('a');

$query = $qb->select(array('DISTINCT a.code', 'a.productCode', 'a.name', 'p.code AS productCode'))
    ->join('AppBundle:CatalogProduct', 'p', 'WITH', 'a.productCode = p.code')
    ->where(
        $qb->expr()->in(
            'p.code',
            $subQuery->getDQL()
        )
    )
    // Parameter used in subquery must be set in main query.
    ->setParameter('code', $catCode)
    ->getQuery();