5

I have a JForm, which inserts records in the database and relies on its own controller, model, view and table, derived by JTable.

However, the database table contains one special field representing the invoice number, which needs to be incremented every new record, and reset at the end of the year, therefore its value can't be taken by the form, but it should be calculated just before saving the record. The query itself is a little more complex of this, but to give an idea it could be hyper-simplified as follows:

INSERT INTO `table` (`number`, `year`, `data`)
VALUES (
    (SELECT MAX(`number`) + 1 FROM `table` WHERE `year` = YEAR(CURDATE())),
    YEAR(CURDATE()),
    'some data...'
)

Now the question, since JTable::store() uses JDatabaseDriver::insertObject() to save its data, and I guess that I can't ask JTable to execute custom SQL statements, what's the best way to achieve this?

2

You can override the store() method in your table class; something like so:

public function store($updateNulls = true) { // or false but you can't nullify a value if needed
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $query->select('MAX(`number`) + 1');
    $query->from('`#__table`');
    $query->where('`year` = YEAR(CURDATE())');
    $db->setQuery($query);
    $db->execute();
    $number = $db->loadResult();

    $date = JFactory::getDate();
    $year = $date->format('Y');

    $this->number = $number;
    $this->year = $year; // or get it by another db query but not recommended
    $this->data = 'some data...';

    return parent::store($updateNulls);
}
1

You can add a check method to your table class. This is run after bind but before store by default. This is a great method to check that the record is valid and handle any specific row requirements.

public function check() {
    // Only set the number if this is a new record (id = 0)
    if (property_exists($this, 'number') && $this->id == 0) {
         $db = JFactory::getDbo();
         $query = $db->getQuery(true);
         $query->select('MAX(`number`) + 1');
         $query->from('`#__table`');
         $query->where('`year` = YEAR(CURDATE())');
         $db->setQuery($query);
         $this->number = $db->loadResult();
    }

    return parent::check();
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.