How to update a column with an int based on order after an editor event

itram

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
How to update a column on all rows with an integer based on order after an editor event.
To be more precise, I would like to execute this SQL query :

set @sequenceNumber=0;
update itemsList
-> set itemOrder=(@sequenceNumber:=@sequenceNumber+1)
-> order by itemOrder ASC;

allan

Are you using our .NET libraries? If so, you could use a server-side event from the library for that. The PHP and NodeJS libraries have similar events.

Allan

itram

Thanks for answering.
I am using php.
I use On preCreate when a new record is added to update all the other records to make room for the new one which works perfectly.

->on('preCreate', function ($editor, $values) {
$editor->db()
->query('update', 'items')
->set('itemOrder', 'itemOrder+1', false)
->where('itemOrder', $values['itemOrder'], '>=')
->exec();
)}

but I can't realize how to convert this SQL sentence:

set @sequenceNumber=0;
update itemsList
-> set itemOrder=(@sequenceNumber:=@sequenceNumber+1)
-> order by itemOrder ASC;

into a server-side event.

allan

I think you'll need to use the sql() method for that which allows you to execute any arbitary SQL:

$editor->db()->sql("SET ...");

Docs for it are available here.

Allan

itram

Thanks Allan, it works perfect.