Doctrine2 subquery leads to Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ','

Question

I just managed to find the SQL query to retrieve the data in the way i wanted it. Now I try to put this into a Doctrine2 query but I am getting the message

Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ','

The SQL-query is somewhat like:

SELECT * 
FROM somedb 
WHERE (config_id,version) IN 
    ( SELECT config_id, MAX(version)
    FROM somedb
    WHERE projectid = 1
    GROUP BY config_id
    ) 
AND project_id = 1

Getting inspired at Doing a WHERE .. IN subquery in Doctrine 2 resulted in the following Doctrine QueryBuilder

$qb = $this->getEntityManager()->createQueryBuilder();
$qb2 = $this->getEntityManager()->createQueryBuilder();

//define subquery
$qb2->select(['somedb.configId', 'MAX(somedb.version)']);
$qb2->from('DataObjects\Entities\someDB', 'somedb');
$qb2->where('somedb.template = :variable');
$qb2->groupBy('somedb.configId');
$qb2->setParameter("variable" , $variable);

//define mainquery
$qb->select('somedb');
$qb->from('DataObjects\Entities\someDB', 'somedb');
$qb->where($qb->expr()->in(['somedb.configId', 'somedb.version'], $qb2->getDQL()));
$qb->andWhere('somedb.template = :variable');
$qb->setParameter("variable" , $variable);

return $qb->getQuery()->execute(null , Query::HYDRATE_ARRAY);

Unfortunately this errormessage doesnt give me a clue what to do and googling it brought no enlightment either. I am quite new to Doctrine and after i held on to the version of the SO post I dont know what to do. Any ideas?

Jonathan

Answer 1

Maybe this solution is not very good, but it solved a similar problem.

// ...   
//define subquery
$qb2->select('CONCAT(somedb1.configId, \'\' ,MAX(somedb1.version)');
$qb2->from('DataObjects\Entities\someDB', 'somedb1');
$qb2->where('somedb1.template = :variable');
$qb2->groupBy('somedb1.configId');


//define mainquery
$qb->select('somedb');
$qb->from('DataObjects\Entities\someDB', 'somedb');
$qb->where($qb->expr()->in('CONCAT(somedb.configId, \'\', somedb.version)', $qb2->getDQL()));

$qb->setParameter("variable" , $variable);