How to use NOW() with DatabaseDriver->insertObject()

Question

I'm inserting an object in a database table using joomla 4, as per joomla docs:

$toInsert = new stdClass();
$toInsert->update_timestamp = 'NOW()';
$toInsert->id = null;

$db = Factory::getContainer()->get('DatabaseDriver');
$db->insertObject('table', $toInsert, 'id');

However, that results in

Incorrect datetime value: 'NOW()' for column 'update_timestamp' at row 1

How do I use NOW() using insertObject()?

Answer 1

If you are going to insert a row into the database using an object, you are going to need to pass in values that do not leverage SQL functions. insertObject() will treat the object's payload as literal values for security/stability.

Another bit of magic is that you must not try to declare a null value in your object's payload. Instead, you should omit the property from the object entirely if you wish NULL to be added for a given database table column.

I don't see anywhere in the insertObject() method's argument signature to allow SQL keywords/functions to be rendered. Unless someone else knows better than me, this means you should use PHP's datetime capabilities to populate the current time.

That said, I think my best advice is to set the column's default value to the current datetime. This way you never need to mention the column when you build an insert object.

A "TRIGGER" can be helpful if you want update update_timestamp after every change made.

Maybe these will inspire:

• MySQL insert with the current timestamp
• How do you set a default value for a MySQL Datetime column?
• How to set default value of MySQL DateTime ( not TIMESTAMP ) to NOW() or Current_DateTIme?

Answer 2

I ended up using arrays to compose the sql query, as per this alternative mentioned in the joomla docs, which works fine:

$columns = array('update_timestamp');
$values = array('NOW()');
$db = Factory::getContainer()->get('DatabaseDriver');
$query = $db->getQuery(true); 
$query->insert($db->quoteName('table'))
      ->columns($db->quoteName($columns))
      ->values(implode(',', $values));