Symfony2: Doctrine subquery in WHERE clause, including LIMIT

I am trying to convert this SQL to DQL or what the query builder option would look like.

select * from project_release r where (select s.title as status_name from release_status_log l left join release_status s on l.release_status_id = s.id where l.release_id = r.id order by l.created_at desc limit 1 ) not in ('Complete', 'Closed') ; 

From inside the repository class for the Release object, I tried this

 return $this->getEntityManager()->createQuery(" select r.* from MyBundle:Release r where (select s.title from MyBundle:ReleaseStatusLog l join l.status s where l.release = r order by l.createdAt desc limit 1 ) IN ('Complete','Closed') order by r.release_date ASC limit 10 ")->getArrayResult(); 

What gives an error

[Syntax error] row 0, column 265: Error: expected Doctrine \ ORM \ Query \ Lexer :: T_CLOSE_PARENTHESIS, received 'limit'

Which refers to limit 1 in the subquery.

So i tried this

 return $this ->createQueryBuilder('r') ->select('r.*') ->where("(select s.title from MyBundle:ReleaseStatusLog l join l.status s where l.release = r order by l.created_at desc limit 1 ) $inClause ('Complete', 'Closed') ") ->setMaxResults( $limit ) ->orderBy('release_date', 'ASC') ->getQuery() ->getArrayResult() ; 

Which gives the same error. How can I execute a subquery limited to 1 row per row in the parent query?

  • Symfony 2.0.15
  • Doctrine 2.1.7
  • PHP 5.3.3
  • MySQL 5.1.52
+4
source share
2 answers

I have a solution for this now. I eventually abandoned my own query system, displaying a set of results from an object in questions.

This is not a great solution, but it works, and until I see another solution, this is the only option with this WHERE clause.

Here's what my search method looks like

 /** * Finds Releases by their current status * * @param array $statuses White-list of status names * @param boolean $blackList Treat $statuses as a black-list * @param integer $limit Limit the number of results returned * @param string $order Sort order, ASC or DESC * * @throws \InvalidArgumentException * * @return array <Release> */ public function findByCurrentStatus( array $statuses, $blackList=false, $limit=null, $order='ASC' ) { if ( empty( $statuses ) ) { throw new \InvalidArgumentException( "Must provide at least one status" ); } $inClause = $blackList ? 'not in' : 'in'; $rsm = new ResultSetMappingBuilder($this->getEntityManager()); $rsm->addRootEntityFromClassMetadata('MyBundle:Release', 'r'); $SQL = " select * from project_release r where (select s.title as status_name from release_status_log l left join release_status s on l.release_status_id = s.id where l.release_id = r.id order by l.created_at desc limit 1 ) $inClause ('" . implode( "','", $statuses ) . "') order by r.release_date $order "; if ( $limit ) { $SQL .= " limit $limit"; } return $this ->getEntityManager() ->createNativeQuery( $SQL, $rsm ) ->getResult() ; } 

I hate a bit, returning to building a query as a string, but ok. Oh, and for you the eyes of an eagle, $statuses does not come from user data, so there are no SQL injection vulnerabilities;)

+1
source

In addition to your own SQL solution, you can create two queries using DQL within the same repository method.

Some setup may be required, but you can try the following:

 public function findCompletedReleases() { $em = $this->getEntityManager(); $dqlSubQuery = <<<SQL SELECT s.title status_name FROM Acme\MyBundle\Entity\ReleaseStatus s, Acme\MyBundle\Entity\ReleaseStatusLog l, Acme\MyBundle\Entity\Release r WHERE l.release = r.id AND l.status = s.id ORDER BY l.createdAt DESC SQL; $statusName = $em->createQuery($dqlSubQuery) ->setMaxResults(1) ->getSingleScalarResult(); $dql = <<<SQL SELECT r FROM Acme\MyBundle\Entity\Release r WHERE :status_name IN ('Complete','Closed') ORDER BY r.release_date ASC SQL; $q = $em->createQuery($dql) ->setParameters(array('status_name' => $statusName)) ->setMaxResults(10); return $q->getArrayResult(); } 
0
source

Source: https://habr.com/ru/post/1485642/


All Articles