Symfony2: Doctrine subquery in the WHERE clause including a LIMIT

Question

I'm trying to convert this SQL into either DQL or whatever the query builder variant 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 entity, I've 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();

Which gives the error

[Syntax Error] line 0, col 265: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'limit'

Which is referring to the limit 1 in the subquery.

So then 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

Answer 1

In addition to your Native SQL solution, you can create two queries using DQL within a single repository method.

Some adjustment may be required but you could try this:

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();
}

Answer 2

I have a solution for this now. I ended up falling back on the native query system with the result set mapping from the entity in questions.

It's not a great solution, but it works and until I see another solution, it's the only option with this type of WHERE clause.

Here's what my finder method looks like now

/**
 * 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 kind of loathed going back to building a query as a string, but oh well. Oh, and for you eagle-eyes, $statuses does not come from user data, so no SQL injection vulnerabilities here ;)