TYPO3 8.x Compare DateTime in CustomRepository (Query, Constraints)

Question

I'm trying to adjust my query using compare field in the TYPO3 repository and don't really get it figured out. Anybody knows how to correctly debug this? I am using TYPO3 8.x CMS. (8.4 to be precise)

<?php 
public function findActiveProducts() {

    $query = $this->createQuery();
    $constraints = array();

    $date = new \DateTime(midnight);
    // $date = new \DateTime(); // i tried to use the precise time to compare
    $today = $date->format('Y-m-d H:i:s');
    // $today = $date->format('Y-m-d'); // 1st try (the field value in db)
    // $today = $date->getTimestamp(); // 2nd try (the type in the modal

    $constraints[] = $query->lessThanOrEqual('entrydate', $today);
    $constraints[] = $query->equals('deleted', 0, false);


        ->matching(
                $query->logicalAnd($constraints)
            )
            ->setLimit(10)
            ->setOrderings(array(
                    'entrydate' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING
                )
            );

    // Some debug's to find out more. sadly didn't work
    \TYPO3\CMS\Core\Utility\DebugUtility::debug( $today , 'today value');
    \TYPO3\CMS\Core\Utility\DebugUtility::debug( $query, 'my query');
    \TYPO3\CMS\Core\Utility\DebugUtility::debug( $constraints, 'constraints');

    $result = $query->execute();




?>

So: Does anybody have a good advise how to debug this? One guy on stackoverflow wrote an entry in another topic explaining we just have to switch on sql errors in TYPO3 and type a mistaken value in the query to output the sql error. This would work, but the error message doesn't last to the field I try to compare. So I'd be very pleased if s.b. would help me out of this misery.

The oldschool debugging no longer works in 8.x, else this would not be a big deal.

    <?php 
            $parser =        \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance('TYPO3\\CMS\\Extbase\\Persistence\\Generic\\Storage\\Typo3DbQueryParser');
            $parser->convertQueryToDoctrineQueryBuilder($query);
            $queryParts = $parser->parseQuery($query);
            \TYPO3\CMS\Core\Utility\DebugUtility::debug($queryParts, 'Query');
    ?>

By the way the model... works fine in all the sections I'm using it, except in the custom query within the repository.

    /**
    * entrydate
    *
    * @var \DateTime
    */
    protected $entrydate = null;

I've also searched stackoverflow but didn't find a fitting solution. - For me, this one doesn't work: How to compare DateTime in Extbase repository - I won't get my query with this one: How to debug a query in extbase? - neither did this: Extbase - get created sql from query

Answer 1

According to the fact, that TYPO3 uses Doctrine as its Connection from TYPO3 v8 on, you can use the Doctrine SQLLoggers to debug your queries, which is a very simple task to do.

/**
 * Description of EntityRepository
 *
 * @author Kevin Ditscheid <kevinditscheid@gmail.com>
 */
class EntityRepository extends \TYPO3\CMS\Extbase\Persistence\Repository{
    /**
     * Find entities by a given DateTime object
     *
     * @param \DateTime $date The DateTime to filter by
     *
     * @return \TYPO3\CMS\Extbase\Persistence\QueryResultInterface
     */
    public function findByDate(\DateTime $date): \TYPO3\CMS\Extbase\Persistence\QueryResultInterface{
        $query = $this->createQuery();
        $queryResult = $query->matching($query->greaterThan('timestampDate', $date))->execute();

        // create a new logger for the database queries to log
        $logger = new \Doctrine\DBAL\Logging\EchoSQLLogger();
        // get the Docrine Connection configuration object
        $connectionConfiguration = $this->getConnectionPool()->getConnectionForTable('tx_sqldebug_domain_model_entity')->getConfiguration();
        // backup the current logger
        $loggerBackup = $connectionConfiguration->getSQLLogger();
        // set our logger as the active logger object of the Doctrine connection
        $connectionConfiguration->setSQLLogger($logger);
        // we need to fetch our results here, to enable doctrine to fetch the results
        $entities = $queryResult->toArray();
        // restore the old logger
        $connectionConfiguration->setSQLLogger($loggerBackup);
        return $queryResult;
    }
    /**
     * Get the ConnectionPool object
     *
     * @return \TYPO3\CMS\Core\Database\ConnectionPool
     */
    protected function getConnectionPool(): \TYPO3\CMS\Core\Database\ConnectionPool{
        return \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance(\TYPO3\CMS\Core\Database\ConnectionPool::class);
    }
}

This example gets the TYPO3 ConnectionPool object, which stores the Database connections TYPO3 is working with. It then gets the specific Connection object for the table we want to work on and gets its Configuration object, which itself is an instance of the class \Doctrine\DBAL\Configuration. Now it can attach an own SQLLogger instance to the configuration object. It uses the Doctrine \Doctrine\DBAL\Logging\EchoSQLLogger which simply var_dumps the queries. It backups and restores the Logger object of the Configuration, just to be save there is nothing lost and later queries will not be var_dumped and flood the output with unnecessary voice. You should now see the queries you have created with your extbase query object.

I created a little example extension for TYPO3 8 here: https://github.com/the-coding-owl/sql_debug

Answer 2

To handle DateTime objects in custom datatabse queries you should use it as a string:

$query->lessThan('modified_on', $date->format('Y-m-d H:i:s')

This is working for me in TYPO3 v8.7.7 with this fixed bug: https://forge.typo3.org/issues/81056

Answer 3

According to https://docs.typo3.org/typo3cms/TCAReference/ColumnsConfig/Type/Input.html#id25 Typo3 dynamically converts your DateTime Objects to Unix timestamps.

So in the resulting SQL Request the WHERE clause will compare an Unix timestamp with a Date Object... Which is like comparing apples with bananas and leads to nothing.

I didn't find a way how you can store Date-objects in the database and compare them with a custom repository, because the 'eval' Parameter is required and converts everything to timestamps.

The solution would be to store your date information as timestamp in the database as well.