Doctrine2 HIDDEN keyword in subquery

timekeepingEntriyRepository.php

<?php

class TimekeepingEntryRepository extends EntityRepository
{
  public function getWorkingHours(array $criteria = null, array $sort = null)
  {
	$employmentContractQueryBuilder = $this->_em->createQueryBuilder();
	$employmentContractQueryBuilder
		->select(array(
			'ec2.id',
			'MAX(ec2.leavingDate) AS HIDDEN active_contract_leaving_date'
		))
		->from('AppBundle\Entity\EmploymentContract', 'ec2')
		->where('ec2.employee = ec.employee')
		->orderBy('active_contract_leaving_date')
		#->orderBy('MAX(ec2.leavingDate)', 'DESC') // [Syntax Error] line 0, col 44978: Error: Expected known function, got 'MAX' 
		->addOrderBy('ec2.updatedAt')
		->setMaxResults(1);

	$qb = $this->_em->createQueryBuilder();
	$qb->select(array(
		'c.id AS contract_id, c.number AS contract_number',
		'e.id AS HIDDEN test',
		->from($this->getClassName(), 'te')
		->join('te.employee', 'e')
		->join('e.employmentContracts', 'ec', JOIN::WITH,
			'te.date BETWEEN ec.startDate AND ec.leavingDate
			AND ec.id IN (' . $employmentContractQueryBuilder->getDql() . ')'
		)
	;
  }
}

The collection employmentContracts holds multiple contracts. I need to LEFT JOIN only the result with the highest leavingDate. There can be multiple rows having the same leavingDate e.g. 2099-12-31.
At the bottom line I only want the JOIN to return a single row from the collection.

[Syntax Error] line 0, col 44878: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ','

Similar discussions:

• https://twitter.com/webdevilopers/status/651114876199288832
• http://stackoverflow.com/questions/6000622/how-to-order-by-count-in-doctrine-2
• http://stackoverflow.com/questions/31453832/symfony-doctrine-use-of-subquery-with-having-clause-without-using-exists

This will be possible in Doctrine 2.5 @jaspernbrouwer:

• http://doctrine-orm.readthedocs.org/en/latest/changelog/migration_2_5.html#dql-improvements

Correcting myself. Though it is allowed to use functions like CONCAT in orderBy, MAX will not work:
Error: Expected known function, got 'MAX'

Should this be posted as an issue or feature request @beberlei @Ocramius ?

👍
I wish COUNT could use MAX or MIN ... (or having HIDDEN work in subqueries !)

Did you have any response from them ?
Thanks

This particular case can be worked around by fixing the query structure.

		->select(array(
			'ec2.id',
			'MAX(ec2.leavingDate) AS HIDDEN active_contract_leaving_date'
		))

to

		->select(array(
			'MAX(ec2.leavingDate) AS  active_contract_leaving_date'
		))

and

		->join('e.employmentContracts', 'ec', JOIN::WITH,
			'te.date BETWEEN ec.startDate AND ec.leavingDate
			AND ec.id IN (' . $employmentContractQueryBuilder->getDql() . ')'
		)

to

		->join('e.employmentContracts', 'ec', JOIN::WITH,
			'te.date BETWEEN ec.startDate AND ec.leavingDate
			AND ec.leavingDate IN (' . $employmentContractQueryBuilder->getDql() . ')'
		)