Using SQL functions with doctrine and TYPO3

Question

I am migrating an old TYPO3-extension to the current build and am trying to change my database access to using doctrine. For the most part that worked great, but now I came upon a few select queries, that make use of SQL-functions e.g. "Year(FROM_UNIXTIME())".

I tried using the sql function as is in the following form:

$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('mytable');

$queryBuilder
     ->select('i.*','c.title AS cat_title','c.uid AS cat_uid')
     ->from('mytable','i')
    ->join('c'
        ...
    )
    ->join('d'
        ...
    )
    ->where(
         $queryBuilder->expr()->eq('Year(FROM_UNIXTIME(i.datetime))', $queryBuilder->createNamedParameter($year, \PDO::PARAM_STR))
    )       

The problem is only with the where part, if i get a read-out on the SQL statement the where clause is simply omitted, as if the line of code never happened.

I then proceeded to make debug statements around the where statement and encapsulate it in a try catch block with a log attached to it: It executes fine, without error, but does nothing.

After google, I now believe, that i would need to implement that function again using the DQL user functions, but the documentation on the typo3 site offers no mention of this and so i am a little unsure as to how to proceed.

Can someone point me in the right direction?

Any help would be much appreciated

Regards Theo

Answer 1

I defaulted to using php to parse the years to full dates and convert them to unix timestamps. The option of using the ORM was there, but simply too much to handle.

I implemented it as follows:

// to convert towards db:
$queryBuilder->createNamedParameter(mktime(0,0,0,1,1, (int)$year), \PDO::PARAM_INT)
// and back:
$queryBuilder->select('i.datetime AS yearb')
// ... the rest of the query seems of litle interest
;
$current = date("Y",$queryBuilder->execute()->fetch()['yearb']);