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
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;)
source share