TYPO3 QueryBuilder - how to find the most recent record for a user?

Question

This is a really obvious data problem, but I can't find a simple solution anywhere.

Using TYPO3 QueryBuilder, how do you select the most recent entry for each user from a table that has multiple entries per user?

uid  user_id  value  crdate
1    1        0      123456
2    1        1      123400
3    2        1      123356
4    2        0      123300

I have tried loads of raw SQL approaches and eventually found a method that works, based on this solution - How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

SELECT * 
FROM `tx_tablename` AS `tt` 
INNER JOIN (
    SELECT `uid`, `user_id`, MAX(`crdate`) AS `MaxDateTime` 
    FROM `tx_tablename` 
    GROUP BY `user_id`
) AS `groupedtt` 
ON `tt`.`user_id` = `groupedtt`.`user_id` 
AND `tt`.`crdate` = `groupedtt`.`MaxDateTime` 
WHERE `tt`.`consent_content` = 3

But I can't see how to reproduce this in QueryBuilder, as the ->join() statement will only accept table names as parameters, not SQL, and ->join() will only accept one joining condition, not two.

Has anyone else found a solution that works in QueryBuilder? Many thanks

Answer 1

The quoting is done in the TYPO3 QueryBuilder. You can bypass it by using the ConcreteQueryBuilder directly.

But doing this, you have to quote identifier yourself, or exceptions would be thrown.

This should do the trick in your pseudo code:

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;
...
$subQueryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
  ->getQueryBuilderForTable('tx_tablename');

$subQuery = $subQueryBuilder
  ->select('uid', 'user_id')
  ->from('tx_tablename')
  ->addSelectLiteral(
    $subQueryBuilder->expr()->max('crdate', 'max_crdate')
  )
  ->groupBy('user_id');

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

$queryResult = $queryBuilder
  ->select('a.*')
  ->from('tx_tablename', 'a')
;

$queryBuilder
    ->getConcreteQueryBuilder()
        ->innerJoin(
            $queryBuilder->quoteIdentifier('a'), // !!! important, quote identifier yourself
            '(' . $subQuery->getSQL() . ')',
            $queryBuilder->quoteIdentifier('b'), // !!! important, quote identifier yourself
            $queryBuilder->expr()->andX(
                $queryBuilder->expr()->eq('a.user_id', $queryBuilder->quoteIdentifier('b.user_id')),
                $queryBuilder->expr()->eq('a.crdate', $queryBuilder->quoteIdentifier('b.max_crdate'))
            ) // andX()
        ) // innerJoin()
;

$queryResult = $queryBuilder->execute();

edit 1

Fixed code example. Need quoteIdentifier() instead of createNamedParam().

Note

If you use nested selects/subselects AND using named parameters, you have to use the outermost queryBuilder instance for named parameter creation, not the current level queryBuilder.

Answer 2

You likely need a sub-query for this. Try the following.

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;
...
$subQueryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
  ->getQueryBuilderForTable('tx_tablename');

$subQuery = $subQueryBuilder
  ->select('uid', 'user_id')
  ->from('tx_tablename')
  ->addSelectLiteral(
    $subQueryBuilder->expr()->max('crdate', 'max_crdate')
  )
  ->groupBy('user_id');

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

$queryResult = $queryBuilder
  ->select('a.*')
  ->from('tx_tablename', 'a')
  ->innerJoin(
    'a',
    '(' . $subQuery->getSQL() . ')',
    'b',
    $queryBuilder->expr()->andX(
      $queryBuilder->expr()->eq('a.user_id', $queryBuilder->createNamedParameter('b.user_id', \PDO::PARAM_STR)),
      $queryBuilder->expr()->eq('a.crdate', $queryBuilder->createNamedParameter('b.max_crdate', \PDO::PARAM_STR))
    )
  )
  ->execute();

However, the code — as it stands now — produces double backticks (`) inside the innerJoin() query. I am not sure how to get rid off them, but the code shows the concept.

Answer 3

You're right — you cannot use sub-queries as arguments in join(), innerJoin(), leftJoin(), and rightJoin() in TYPO3 as these values are escaped using quoteIdentifier() (see TYPO3 v10.2 source code at GitHub) and backticks added.

I wonder if the following SQL query returns the result you are after:

SELECT `uid`, `user_id`, value, MAX(`crdate`)
FROM `tx_tablename`
GROUP BY `user_id`
HAVING MAX(`crdate`);

In this case the Doctrine code would look like the following:

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;
...
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
  ->getQueryBuilderForTable('tx_tablename');

$queryResult = $queryBuilder
  ->select('uid', 'user_id', 'value')
  ->from('tx_tablename')
  ->addSelectLiteral(
    $queryBuilder->expr()->max('crdate', 'crdate')
  )
  ->add('having', 'MAX(`crdate`)')
  ->groupBy('user_id')
  ->execute();