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?