Doing a Where .. in Subquery in Doctrine 2

Doing a WHERE .. IN subquery in Doctrine 2

This is how I would try it:

/** @var Doctrine\ORM\EntityManager $em */
$expr = $em->getExpressionBuilder();
$em->createQueryBuilder()
   ->select(array('DISTINCT i.id', 'i.name', 'o.name'))
   ->from('Item', 'i')
   ->join('i.order', 'o')
   ->where(
       $expr->in(
           'o.id',
           $em->createQueryBuilder()
               ->select('o2.id')
               ->from('Order', 'o2')
               ->join('Item', 
                      'i2', 
                      \Doctrine\ORM\Query\Expr\Join::WITH, 
                      $expr->andX(
                          $expr->eq('i2.order', 'o2'),
                          $expr->eq('i2.id', '?1')
                      )
               )
               ->getDQL()
       )
   )
   ->andWhere($expr->neq('i.id', '?2'))
   ->orderBy('o.orderdate', 'DESC')
   ->setParameter(1, 5)
   ->setParameter(2, 5)
   ;

I didn't test this of course, and made some assumptions about your models. Possible problems:

• Limit: this has been somewhat of a problem in Doctrine 2, it seems query builder is not very good at accepting limits. Do take a look here, here and here.
• The IN clause is usually used with an array, but I think it will work with a subquery.
• You probably can use the same parameter ?1, instead of two parameters (because they're the same value), but I'm not sure.

Concluding, this may not work first time, but will surely put you on the right track. Do tell us the final 100% correct answer afterwards.

Doing a subquery inside a where with Doctrine 2 querybuilder

For people interested I finally found a solution. I did the query like this (if someone has another solution I am very interested):

public function getConversations()
{
    $rawSql = "SELECT * FROM `message` A 
                WHERE A.id =
                (SELECT B.id FROM `message` B 
                WHERE (A.user_id = B.user_id AND A.receiver_id = B.receiver_id) OR (A.user_id = B.receiver_id AND A.receiver_id = B.user_id)
                ORDER BY creationdate DESC 
                LIMIT 1)
                ";

    $stmt = $this->getEntityManager()->getConnection()->prepare($rawSql);
    $stmt->execute([]);

    return $stmt->fetchAll();
}

WHERE ... IN query with sub-query in Doctrine queryBuilder or equivalent

your query can be turned into something without a sub-query, but with a join instead, which should be equivalent (and should have the same runtime/complexity)

SELECT u 
FROM user u 
LEFT JOIN user am ON (am.id=u.accountManager) 
WHERE am.roles LIKE '%ROLE_MANAGER%' 
  AND am.accountManager=:managerAdminId
  AND u.roles LIKE '%ROLE_PUBLISHER%'

which can be translated into querybuilder accordingly (I have to assume, that you did not define your associations ... which I find disturbing, but you probably have your reasons):

return $this->createQueryBuilder('u')
  ->leftJoin('App\Entity\User', 'am', 'WITH', 'am.id=u.accountManager')
  ->andWhere('am.roles LIKE :role')
  ->setParameter('role', '%ROLE_MANAGER%')
  ->andWhere('am.accountManager = :managerAdminId')
  ->setParameter('managerAdminId', $managerAdminId)
  ->andWhere('u.roles LIKE :role2')
  ->setParameter('role2', '%ROLE_PUBLISHER%')
  ->getQuery()->getResult();

there is also the options of actually using sub-queries, but using sub-queries imho is always inconvenient - and ugly.

(you might have a look into writing just plain DQL queries, you might feel more at home ...?)

Doctrine subquery andwhere

For such cases, you can rely on the Expr class which contains the methods you need to build those more complex queries. Everything you need to know is explained in the Doctrine documentation in the Query Builder section.

The current documentation actually gives an example which almost fits with the use case you mentioned. You could write something like this for example:

$qb->andWhere($qb->expr()->orX(
   $qb->expr()->eq('u.id', '?1'),
   $qb->expr()->like('u.nickname', '?2')
));

The main advantage of relying on the Expr class is that you can pretty much write any kind of combination with it and it will produce the right DQL for it. It can get pretty verbose however.

That being said, you can also directly write your OR condition in the andWhere:

$qb->andWhere('u.id = ?1 OR u.nickname = ?2');

Doctrine DQL - nested subqueries in WHERE clause throw Syntax Error

Correct, the DBAL does not support arithmetic operations on subqueries. Additional the DBAL does not support joined subqueries, JOIN (SELECT FROM...) ON without using a native query.

Another issue is the WHERE statement of your subqueries, being dependant on the root query, will cause your root query to perform a full table scan. Executing each of the subqueries per-row, unless a criteria is added to the root query (WHERE o.id ...).

As the subquery SUM values are dependant on the root query id. You can rewrite the query to use a subquery as a hidden column and a HAVING clause, to filter the id result set from the added column results.

$em = $queryBuilder->getEntityManager();
$expr = $em->getExpressionBuilder();

$qbPC = $em->createQueryBuilder()
    ->select('COALESCE(SUM(pc.residentialUnits), 0)')
    ->from(App\Entity\PropertyConnection::class, 'pc')
    ->where($expr->eq('pc.contract', "$rootAlias.id"));

$qbPWA = $em->createQueryBuilder()
    ->select('COALESCE(SUM(pwa.residential_units), 0)')
    ->from(App\Entity\PropertyWithoutAddress::class, 'pwa')
    ->where($expr->eq('pwa.contract', "$rootAlias.id"));

$qb = $this->getEntityManager(Contract::class)
    ->createQueryBuilder()
    ->select('o.id')
    ->from(App\Entity\Contract::class, 'o')
    ->addSelect('(' . $qbPC->getDQL() . ') AS HIDDEN pc_ru')
    ->addSelect('(' . $qbPWA->getDQL() . ') AS HIDDEN pwa_ru')
    ->having($expr->lte(':v', 'pc_ru + pwa_ru'))
    ->setParameter('v', $params['rangeFrom']);

dump($qb->getDQL());

Resulting DQL

SELECT 
    o.id, 
    (SELECT 
         COALESCE(SUM(pc.residentialUnits), 0) 
     FROM App\Entity\PropertyConnection pc 
     WHERE pc.contract = o.id
    ) AS HIDDEN pc_ru, 
    (SELECT 
         COALESCE(SUM(pwa.residential_units), 0) 
     FROM App\Entity\PropertyWithoutAddress pwa 
     WHERE pwa.contract = o.id
    ) AS HIDDEN pwa_ru 
FROM App\Entity\Contract o 
HAVING :v <= pc_ru + pwa_ru

---

Related Topics