Unable to update NULL via updateObject

Question

We got 2 tables where one table is parent and another table as a child with primary and foreign key reference.

Assume table1 is the parent and table2 is the child as below.

table1:

id  <- primary key of table1
name  <- name field

table2:

id  <- primary key of table2 auto increment
par_id  <- foreign key referring table1 default to NULL
position  <- posiiton field

The code snippet below is to update an existing record in the child table with par_id as NULL

$recObj = new stdClass ();
$recObj->id = '1';
$recObj->par_id=null
$recObj->position="test position"

$db->updateObject('#__table2', $recObj, 'id');

When the above code executes, it is not updating the par_id to null. Instead the old value remains.

Answer 1

Found a way by adding an another parameter as below:

$db->updateObject('#__table2', $recObj, 'id', true);

The 4th parameter determines whether null values can be used or not. Default this will be set as false.

https://docs.joomla.org/API17:JDatabase::updateObject

Answer 2

The way it should be done

For now (until they introduce full entities support somewhere in Joomla! 4.x) it is better to use JTable to store "entities". In that way you can simply set $updateNulls parameter in JTable::store() method o true. It also simplifies a lot of CRUD and validate operations making your programming faster. You can read more in a JTable documentation or look for tables usage in core components.

How to perform nulls update in updateObject()

The second method is to provide the fourth parameter to JDatabase::updateObject() method the way you did. As far as I remember it is used in JTable::store() internaly.

If there is no API method for your case

As a last resort you can always run a direct query:

use Joomla\CMS\Factory;

$db = Factory::getDbo();
$q = $db->getQuery(true);
$q
    ->update('#__table2')
    ->set('b = NULL')
    ->where('id=1');
$db->setQuery($q);
$db->execute();