update column data

update column data

HordovenkoHordovenko Posts: 24Questions: 2Answers: 0

Hello.
I'm using Editor.
I need update data in column "record length" after operation insert or delete.
A row has column "record start". For "record end" using "record start" next row.

The insert operation is performed one row at a time. The delete operation is performed one row at a time and many rows at a time (rows can be selected differently).
In this case I thing the best way - update all rows at table (column "record length").

I have not found in the forum how to do it.
Suggest the most effective way.

I would be grateful for help.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    Hi,

    I'm afraid I don't fully understand. Could you illustrate this for me with some example data?

    Thanks,
    Allan

  • HordovenkoHordovenko Posts: 24Questions: 2Answers: 0

    Hi, @allan
    The table structure is shown here - http://live.datatables.net/lomilewa/4/edit

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @Hordovenko ,

    In this case I thing the best way - update all rows at table (column "record length").

    Yep, I just scan the rows with table.rows({order:'current'}), and modify that record_start column with the current sum of record_length.

    Cheers,

    Colin

  • HordovenkoHordovenko Posts: 24Questions: 2Answers: 0

    hi @colin
    Only the record_length column changes after the delete operation.
    Update algorithm is next:
    record_length(current row) = record_start(next row) - record_start(current row).

    How to implement this algorithm for the table?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @Hordovenko ,

    Personally, I would say if the record count is low, it would make sense to remove the record_length from the database and just work it out on the client. If you need it in the DB, and the record count is high, it would be better to do it on the server, since the client would be issuing multiple edits to update all the records...

    Cheers,

    Colin

  • HordovenkoHordovenko Posts: 24Questions: 2Answers: 0

    hi @colin
    Thank you for your reply.
    I plan to do this on a client.
    But I do not know what mechanism is better to use. Use a cycle for or something else?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Are you expecting the changes to only be on the client - or do you want each update sent back to the server? That's the question I was asking in my last message.

  • HordovenkoHordovenko Posts: 24Questions: 2Answers: 0

    hi @colin.
    I'm sorry I didn't fully understand.
    I want each update sent back to the server.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    Answer ✓

    Hi @Hordovenko ,

    I think this here is doing what you want - it's updating the records when the table is changed.

    Cheers,

    Colin

  • HordovenkoHordovenko Posts: 24Questions: 2Answers: 0

    hi @colin.
    Thank you very much for your reply.

    I have already found a solution to the problem. It is very similar to yours.
    But I used MultiSet and put Submit after the for loop in the hope that the update all rows will execute one request

    for (let i = 0;... {
    ...
    editor.field('length').multiSet( x , y);
    }
    editor.submit();

    But the request takes a very long time. For example, if the table has 100 rows (at the front), the query is executed about 10 seconds.
    I was looking at a database (PostgreSQL) log. Each line is updated individually.

    Please tell me how to speed up the query.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @Hordovenko ,

    You would need to do the updates on the server if you'd want it to be any faster. As I said in an earlier comment above:

    If you need it in the DB, and the record count is high, it would be better to do it on the server, since the client would be issuing multiple edits to update all the records...

    Cheers,

    Colin

  • HordovenkoHordovenko Posts: 24Questions: 2Answers: 0

    hi @colin
    So, I misunderstood you.
    I need to do the updates on the server.
    Please tell me what to do in that case.

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    What you are looking for is not to dissimilar to our RowReordering example on delete. There we use a preRemove server-side event to issue commands to the database to update:

        ->on( 'preRemove', function ( $editor, $id, $values ) {
            // On remove, the sequence needs to be updated to decrement all rows
            // beyond the deleted row. Get the current reading order by id (don't
            // use the submitted value in case of a multi-row delete).
            $order = $editor->db()
                ->select( 'audiobooks', 'readingOrder', array('id' => $id) )
                ->fetch();
     
            $editor->db()
                ->query( 'update', 'audiobooks' )
                ->set( 'readingOrder', 'readingOrder-1', false )
                ->where( 'readingOrder', $order['readingOrder'], '>' )
                ->exec();
        } )
    

    The other option would be to use a database trigger rather than a server-side event. If it were an app I was in complete control of the database structure of, a trigger is how I'd do it personally, since then you can use any client to connect to the database and make the required changes (e.g. delete a row from phpMyAdmin).

    Allan

This discussion has been closed.