How to use MySQL FIELD-function in TYPO3 8.7 with Doctrine DBAL

When you want to query an Extbase repository for a list of UIDs or PIDs, you usually add a query constraint like query->in('pid', $pidList) to the query, where $pidList is an array of integers. But what, if you want to control the sorting of the returned records? Lets assume, you want to select the following list of UIDs [5, 3, 4, 1] from your repository and the sorting or the UIDs must remain.
Extbase only has the possibility to sort the query result by a given column either ascending or descending, so there is no possibility to control so returned sorting as intended.

In order to resolve the problem, I found this very helpful article from Manfred Rutschmann, where he describes exactly the same situation and offers a solution for the problem, which works great in TYPO3 7.6. Sadly the solution does not work in TYPO3 8.7 LTS and fails with an exception, that e.g. column tx_myext_domain_model_mymodel.uid=5 does not exist.

Since I'm using MySQL as a database engine, I tried to find a solution, where I could use the MySQL FIELD-function to apply the special sorting to the ORDER BY clause.

Fortunately Doctrine DBAL was integrated in TYPO3 8.7 LTS, which offers an easy and readable way to construct my query. I added the following function to my Extbase repository.

	// The use statements for the fileheader
	use MyVendor\MyExtension\Domain\Model\Mymodel;
	use TYPO3\CMS\Core\Database\ConnectionPool;
	use TYPO3\CMS\Core\Database\Query\QueryBuilder;
	use TYPO3\CMS\Core\Utility\GeneralUtility;
	use TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper;
	/**
	* Returns all matching records for the given list of uids and applies the uidList sorting for the result
	*
	* @param string $uidList
	* @return array|\TYPO3\CMS\Extbase\Persistence\QueryResultInterface
	*/
	public function findByUidList($uidList)
	{
	$uids = GeneralUtility::intExplode(',', $uidList, true);
	if ($uidList === '' || count($uids) === 0) {
	return [];
	}
	$dataMapper = GeneralUtility::makeInstance(DataMapper::class);
	/** @var QueryBuilder $queryBuilder */
	$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
	->getQueryBuilderForTable('tx_myext_domain_model_mymodel');
	$rows = $queryBuilder
	->select('*')
	->from('tx_myext_domain_model_mymodel')
	->where($queryBuilder->expr()->in('uid', $uids))
	->add('orderBy', 'FIELD(tx_myext_domain_model_mymodel.uid,' . implode(',', $uids) . ')')
	->execute()
	->fetchAll();
	return $dataMapper->map(Mymodel::class, $rows);
	}

view raw ExtbaseMySQLOrderFunction.php hosted with ❤ by GitHub

In line 31 I add my own ORDER BY FIELD clause, where the special sorting is applied

Since the queryBuilder just returns an array of database records (where each records is just an array of returned fields/values), I use the TYPO3 DataMapper in line 35 to map the returned rows as objects, so the returned QueryResult object will contain objects of the type Mymodel

The example shown has one little downside, since it only works with a MySQL Database.

If you want to get more details about the Doctrine DBAL integration in TYPO3, make sure to read the documentation.