A foreign key field not updating as NULL

Question

I am having a field 'leavepack_id' in a table with the below table definition

CREATE TABLE IF NOT EXISTS `#__position` (
  `position_id` int(11) NOT NULL AUTO_INCREMENT,
  `leavepack_id` int(9) DEFAULT NULL,
  PRIMARY KEY (`position_id`),
  FOREIGN KEY (`leavepack_id`) REFERENCES `#__leavepack`(`leavpack_id` ON UPDATE CASCADE ON DELETE SET NUL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

By the above definition, the field leavepack_id can accept a valid id or NULL.

Here is the code I have in my model

if ($data['leavepack_id'] == '')  $data['leavepack_id'] = NULL;
$success = parent::save($data);

The problem:

When an existing record which has a valid id is saved with null then the update retains the ID as is instead of storing as NULL.

In other words:

Assume the existing record has position_id = 1 and leavepack_id = 2. When updating the value comes as position_id = 1 and leavepack_id = NULL. The problem is NULL is not stored.

After debugging some of the core Joomla files, I am able to find the root cause. The bind() function in JTable ignores the field that has NULL value.

Can someone please help me on this.

Answer 1

I managed to override the model save() method by calling the bind() and store() and store NULL values.

    if ($data['leavepack_id'] == '')  $data['leavepack_id'] = NULL;

    $table = $this->getTable();
    if(!$table->bind($data)){
        $this->setError($this->_db->getErrorMsg());
        return false;
    }

    if(!$table->store(true)){
        $this->setError($this->_db->getErrorMsg());
        return false;
    }

Not 100% sure whether this is the right way but it works for my case.

Answer 2

You should take care of this in JTable only.

This part:

if ($data['leavepack_id'] == '')  $data['leavepack_id'] = NULL;

you should insert in JTable::check() like this:

public function check(){
    if ($this->leavepack_id == '')  $this->leavepack_id = NULL;

    return parent::check();
}

and then override store function like this:

public function store($updateNulls = true){
    return parent::store($updateNulls);
}

If you make it like this not only this functionality is available in every place that you want to use this table (this JTable reference) but also makes it possible to disable nulls update on demand.