Covert SQL query to TYPO3 query builder?

Question

How to convert SQL query to TYPO3 query builder.

SELECT * FROM tableName ORDER BY (CASE WHEN DATE(dateColumn) < DATE(GETDATE()) THEN 1 ELSE 0
     END) DESC, dateColumn ASC

enter link description here

Same functionality i need in typo3 query builder.

Stefan Bürk Stefan Bürk · Accepted Answer · 2020-04-02T21:53:39

To get the sql query you posted, you can do it like following:

// little helper function to debug querybuilder,
// queries with parameter placeholders
function debugQuery(QueryBuilder $builder)
{
    $preparedStatement  = $builder->getSQL();
    $parameters         = $builder->getParameters();
    $stringParams = [];
    foreach ($parameters as $key => $parameter) {
        $stringParams[':' . $key] = $parameter;
    }
    return strtr($preparedStatement, $stringParams);
}

// get querybuilder
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getConnectionByName('Default')
    ->createQueryBuilder();

// build query
$queryBuilder
    ->select('*')
    ->from('table')
    ->getConcreteQueryBuilder()->orderBy(
        '(CASE WHEN DATE('
          .$queryBuilder->quoteIdentifier('dateColumn')
          .') < DATE(GETDATE()) THEN 1 ELSE 0 END)',
        'DESC'
    )
;
$queryBuilder->addOrderBy('dateColumn', 'ASC');

// build query
$sql = debugQuery($queryBuilder);

The generates following sql query:

SELECT 

FROM `table` 
ORDER BY 
  (CASE WHEN DATE(`dateColumn`) < DATE(GETDATE()) THEN 1 ELSE 0 END) DESC, 
  `dateColumn` ASC

Some note beside: To my knowlage GETDATE() is not a valid mysql method, more MSSQL. Eventually you want CURRENT_DATE() instead.

edit #1

Not tested/run .. just created the sql string to match what you provided. So don't blame me if provided sql query is wrong itself.

Covert SQL query to TYPO3 query builder?

1 Answers