Doing a WHERE .. IN subquery in Doctrine 2

by

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.

More Related Contents:

  1. Which one of these Queries are wrong and which is correct (SQL) [closed]
  2. SQL Selecting from three tables
  3. Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc
  4. How to implement a many-to-many relationship in PostgreSQL?
  5. Entity Attribute Value Database vs. strict Relational Model Ecommerce
  6. How can I schedule a job to run a SQL query daily?
  7. Reset identity seed after deleting records in SQL Server
  8. What is the difference between JOIN and UNION?
  9. What is atomicity in dbms
  10. SQLite – How do you join tables from different databases?
  11. MySQL dump by query
  12. SQL ROWNUM how to return rows between a specific range
  13. How would you implement sequences in Microsoft SQL Server?
  14. SQL how to compare two tables for same data content?
  15. What does =* mean?
  16. Does MS Access support “CASE WHEN” clause if connect with ODBC?
  17. MySQL equivalent of DECODE function in Oracle
  18. What datatype to use when storing latitude and longitude data in SQL databases? [duplicate]
  19. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  20. Favourite performance tuning tricks [closed]
  21. How do I alter the position of a column in a PostgreSQL database table?
  22. Conditional logic in PostDeployment.sql script using SQLCMD
  23. Is it necessary to create tables each time you connect the derby database?
  24. Venn Diagram for Natural Join
  25. Database efficiency – table per user vs. table of users
  26. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  27. Find rows that have the same value on a column in MySQL
  28. what is the difference between GROUP BY and ORDER BY in sql
  29. Temporal database design, with a twist (live vs draft rows)
  30. Executing SQL query on multiple databases

Leave a Comment