Joomla 3.9 replace zero dates with null on save/checkin (mysql zero date deprecated)

Question

So zero dates are deprecated in MySQL. J4 has replaced them with nulls.I am trying to update a J3 component to stop J setting zeros in the modified and checked_out_time fields.

For modified I am using table prepareTable() to set an empty modified date from the form to NULL for saving and table getItem() to replace nulls in modified with a zero date to keep the calendar control happy.

The problem is with checked_out_time which is set back to zero by joomla in a separate query when the item is closed (ie after save).

How can I override this to force Joomla to save a valid for MySQL date?

Answer 1

With thanks to @glenn-arkell who pointed me in the right direction, for j3.9 this works to stop Joomla writing '0000-00-00 00:00:00' into the checked_out_time field. For other fields (eg modified and any of your own datetime fields) use bind() or prepareTable() and getItem() to set empty dates from a form control to null and null dates back to zero for use in a form control.

In your table class add the following function (copied from the parent table class and modified to write nulls for the checked_out_time field. The parent class is replaced.

    public function checkIn($pk = null) {
    $checkedOutField = $this->getColumnAlias('checked_out');
    $checkedOutTimeField = $this->getColumnAlias('checked_out_time');

    // If there is no checked_out or checked_out_time field, just return true.
    if (!property_exists($this, $checkedOutField) || !property_exists($this, $checkedOutTimeField))
    {
        return true;
    }

    if (is_null($pk))
    {
        $pk = array();

        foreach ($this->_tbl_keys as $key)
        {
            $pk[$this->$key] = $this->$key;
        }
    }
    elseif (!is_array($pk))
    {
        $pk = array($this->_tbl_key => $pk);
    }

    foreach ($this->_tbl_keys as $key)
    {
        $pk[$key] = empty($pk[$key]) ? $this->$key : $pk[$key];

        if ($pk[$key] === null)
        {
            throw new \UnexpectedValueException('Null primary key not allowed.');
        }
    }

    // Check the row in by primary key.
    $query = $this->_db->getQuery(true)
        ->update($this->_tbl)
        ->set($this->_db->quoteName($checkedOutField) . ' = 0' )
        ->set($this->_db->quoteName($checkedOutTimeField) . ' = NULL' );
    parent::appendPrimaryKeys($query, $pk);
    $this->_db->setQuery($query);

    // Check for a database error.
    $this->_db->execute();

    // Set table values in the object.
    $this->$checkedOutField     =  0;
    //we are putting an empty, not null, value here for compatibility with anything else using onAfterCheckin.
    $this->$checkedOutTimeField =  '';

    $dispatcher = \JEventDispatcher::getInstance();
    $dispatcher->trigger('onAfterCheckin', array($this->_tbl));

    return true;
}

This seems to work ok for getting rid of those pesky '0000-00-00 00:00:00' values in the checked_out_time field in the database and stops mysql throwing warnings and allows phpmyadmin to work without having to change the Strict mode.

Answer 2

Some added suggestions around null dates - I came upon the need to remove a date from a datetime field using the standard Table from the Model class. This makes the removal of a date nice and clean. This may be obvious to some.

$updateNulls = true;
$table = $this->getTable();
$table->load($id);

$table->proved_date = NULL;

$table->store($updateNulls);