Doctrine Subquery Left join

Question

Please tell me what am I doing wrong? What are the possibilities to outwit Doctriny and use a subquery for left join through DQL

My code

        $subQuery = $this->em->createQueryBuilder()
            ->select('apd')
            ->from(TABLE2::class, 'apd')
            ->where('apd.status = :status')
            ->andWhere('apd.date > :date')
            ->getDql();

        $query =$this->em->createQueryBuilder()
                ->select(
                    "date_format(c.dateCall , '%Y-%m-%d') as date"
                )
                ->from(TABLE1::class, 'c')
                ->leftJoin(
                    sprintf('(%s)', $subQuery),
                    'apd',
                    Join::WITH,
                    'c.column = apd.column'
                )
                ->where('c.dateCall >= :date')
                ->setParameter('date', '2023-07-10')
                ->setParameter('status', 'active')
                ->setParameter('date', $date)
                ->groupBy('p.name, dates')
                ->getQuery()
                ->getResult();

Error

[Semantical Error] line 0, col 275 near 'JOIN (SELECT': Error: Subquery is not supported here

It is impossible through createnativequery, it is necessary with the help of DQL

Answer 1

I am not sure what you are trying to achieve but I think it might not be necessary to try to join a subquery in your example. Could you not just join Table2 and add the where conditions to the main query? I was thinking like this:

$query =$this->em->createQueryBuilder()
    ->select(
        "date_format(c.dateCall , '%Y-%m-%d') as date"
    )
    ->from(TABLE1::class, 'c')
    ->leftJoin('c.table2', 'apd')
    ->where('c.dateCall >= 2023-07-10')
    ->andWhere('apd.status = :status')
    ->andWhere('apd.date > :date')
    ->setParameter('status', 'active')
    ->setParameter('date', $date)
    ->groupBy('p.name, dates')
    ->getQuery()
    ->getResult();

Keep in mind, I don't know which 'date' Parameter was supposed to go where, you'll have to adjust as you see fit, but it's not possible to have two parameters that have the same name. You can either do it like above or name the second parameter slightly differently.

I am also not sure where 'p.name' is coming from, as you have not defined an alias 'p'. If you are using a third table, you should make sure to join that one as well.

Hope that helps!