TYPO3 QueryBuilder: Howto handle fieldName calculations?

Question

(TYPO3 v11)

There is a table of events. Each event has a datetime_end field and a days_until_deletionfield. A scheduler script should delete old events automatically.

In SQL this works:

UPDATE `tx_myevents_domain_model_event` SET deleted = 1 
WHERE UNIX_TIMESTAMP() > (datetime_end + (days_until_deletion * 86400));

Is there a way to reproduce this SQL statement with the querybuilder? Something like:

$queryBuilder->where(
    $queryBuilder->expr()->lt( ('datetime_end' + ('days_until_deletion' * 86400)), time()
)

Garvin Hicking · Accepted Answer · 2025-06-20 10:48:16Z

2

This here should work - note that Doctrine DBAL doesn't support arithmetic expressions, but does allow to pass raw WHERE SQL statements. You'll need to ensure yourself the SQL you write is compatible to your target SQL systems (for example PostgreSQL has no "UNIX_TIMESTAMP()" function)

$queryBuilder->update('tx_myevents_domain_model_event')
   ->set('deleted', 1)
   ->where('UNIX_TIMESTAMP() > (datetime_end + (days_until_deletion * 86400))');
$queryBuilder->executeStatement();

answered Jun 20 at 10:48

Garvin Hicking

6312 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stefan Padberg Jun 20 at 13:04

Good to know. For me, it is cleaner to have this SQL code "encapsulated" with the add function. If problems arise in the future according to change of database systems, it is easier to find.

Stefan Bürk Nov 21 at 11:35

add() is dropped by doctrine dbal in newer versions. Unrelated to the used technique, even with plain concentated string and Connection->executeStatement()` you have your method/place using the database call covered with functional tests - right ? And switching the db version/vendor would reveal issues witht the tests already before deployment. Beide that, not getting why add() should encapsulate anything.

Stefan Padberg · Accepted Answer · 2025-06-20 12:57:05Z

1

I found a way which maybe is useful for anybody.

As I want to change the deleted field, restrictions have to be removed:
To handle complicated SQL situations the add() function is appropriate.

Thus, this code works:

$queryBuilder = $this->connectionPool->getQueryBuilderForTable(tx_myevents_domain_model_event);
$queryBuilder
    ->getRestrictions()
    ->removeAll();
$queryBuilder
    ->update(tx_myevents_domain_model_event)
    ->add( 'where', '(datetime_end + (days_until_deletion * 86400)) < UNIX_TIMESTAMP()')
    ->set('deleted', '1');

answered Jun 20 at 12:57

Stefan Padberg

5872 silver badges20 bronze badges

4 Comments

Heinz Schilling Jun 21 at 7:38

Based on our experience, I recommend a different approach. Delete the data records via TYPO3 functions. Advantages: - TYPO3 sets timestamps, user (CLI) - TYPO3 resolves dependencies, for example to files (sys_file_reference)

Stefan Padberg Jul 3 at 6:53

Can you give an example or a hint for what you call TYPO3 functions?

Stefan Bürk Jul 4 at 8:35

add() should not be used, and vanish in newer Doctrine DBAL / TYPO3 version anyway. The correct api to use are where(), andWhere() and orWhere(). All the example omit proper quiting ... and I would resort using sprintf() here and have at least the fieldnames with placeholders and set as sprintf values using $queryBuilder->quoteIdentifier('datetime_end') for example. As already mentioned, i's better to use DataHandler cmdMap for delete a record, which keeps track of that in the history (audit) and automatically checks for soft-delete/hard delete depending on table/tca support.

Stefan Padberg Oct 13 at 12:08

This issue is not forgotten. I will return to it as soon as possible.

Heinz Schilling · Accepted Answer · 2025-07-27 09:25:43Z

0

You can use this function, for example.


/** @var DataHandler $dataHandler */
$dataHandler = GeneralUtility::makeInstance(DataHandler::class);

$cmd = [];
foreach ($queryBuilder->executeQuery()->fetchFirstColumn() as $uid){
    $cmd[$table][$uid]['delete'] = 1;
}

$dataHandler->start([], $cmd);
$dataHandler->process_cmdmap();

answered Jul 27 at 9:25

Heinz Schilling

2,3474 gold badges23 silver badges36 bronze badges

Collectives™ on Stack Overflow

TYPO3 QueryBuilder: Howto handle fieldName calculations?

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related