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

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

itramitram Posts: 41Questions: 14Answers: 0

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;

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin

    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

  • itramitram Posts: 41Questions: 14Answers: 0

    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.

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin
    Answer ✓

    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

  • itramitram Posts: 41Questions: 14Answers: 0

    Thanks Allan, it works perfect.

This discussion has been closed.