How to update _other_ rows after edit?

co-operate

I'm using DataTables with Editor to edit a MySQL table. I need to implement a custom logic when editing the "single" column. Here's my table structure and initial data:

CREATE TABLE tasks (
    id INT PRIMARY KEY,
    `group` VARCHAR(50),
    sum INT,
    single INT,
    result VARCHAR(10)
);

INSERT INTO tasks VALUES
(1, 'task_a', 100, 5, 'fail'),
(2, 'task_a', 100, 94, 'fail'),
(3, 'task_a', 100, 2, 'fail'),
(4, 'task_b', 20, 20, 'success'),
(5, 'task_b', 20, 0, 'success');

I've set up DataTables Editor to edit only the single column, and now I want to implement the following logic when this single column is edited:
When a value for group task_a is changed, collect other all rows where group is also task_a and calculate the sum of all single values.
If the calculated sum equals the sum value of the edited row, then update all rows where group is task_a to result => success.

How can I implement this logic using DataTables Editor? I'm particularly unsure about:

1. How to update multiple rows based on this calculation.
2. How to refresh the DataTable to show the updated results.

rf1234

Use "writeCreate" and "writeEdit". Then make the calculations and your db-updates as required.
https://editor.datatables.net/manual/php/events

If you want to do the db-updates using Editor's db methods here is more about this:
https://datatables.net/forums/discussion/80224/child-created-updated-upon-creation-update-of-the-parent

co-operate

@rf1234, thanks for this suggestion and especially thanks for your helpful update() and $db->insert() examples">examples on $db->update() and $db->insert()!

I've resorted to "postEdit" and "postCreate" instead, as I need the full $row in order to access the group value needed for updating other rows of that samegroup. $row seems to be available in postEdit/Create methods, only.

However, neither will trigger a refresh of these other updated rows. Hence, my follow-up question:

Is it possible to have my PHP postEdit event trigger a table refresh, and in particular a refresh of only the updated rows? (A complete table refresh would take too much time.)

rf1234

If you are using the "post" events instead of the "write" events, your SQL updates won't be taken into account because the data will already have been read back from the server!

The best would probably be to use the "write" events and make an SQL SELECT to fetch the additional data needed to make the INSERT and the UPDATE.

Here are a few more examples that show you how flexible this can be. I use quite a bit of custom SQL because I use Editor for every CRUD operation in all of my modules - even if it isn't straightforward to use Editor.

Field::inst( 'ctr.id AS ctr_has_ctr' )->set( false )   //return same format as an MJoin   
    //make sure copy & new also works for this field!!
    //xss expects a string but we send an array!!
    ->xss( false )
    ->getFormatter( function($val, $data, $opts) use ( $db ){
        $stmt = ('SELECT b.linked_ctr_id, a.serial, a.ctr_name
                    FROM ctr a  
              INNER JOIN ctr_has_ctr b ON a.id = b.linked_ctr_id
                   WHERE b.ctr_id = :ctr_id 
                ORDER BY 1 ASC');  
        $result = $db ->raw()
                      ->bind(':ctr_id',$val)
                      ->exec($stmt);
        return $result->fetchAll(PDO::FETCH_ASSOC);
    } ),

.....
$result = $db->raw()
    ->bind( ':id', $manualId )  
    ->exec( 'SELECT counterparty 
               FROM gov_manual_creditor  
              WHERE id = :id' );
$row = $result->fetch(PDO::FETCH_ASSOC);
if ( is_null( $row["counterparty"] ) ) {
    return '';
} 
return (int)$row["counterparty"];

....
$sumPlanned = 0;
$res = $db->raw()
    ->bind( ':ctr_govdept_id', $val["ctr_govdept"]["id"] )
    ->bind( ':year', $year )  
    ->exec( 'SELECT COALESCE(SUM(a.cashflow_amount), 0)      AS planned_funds_call
               FROM sub_exec_cashflow a 
         INNER JOIN ctr_has_ctr_govdept b ON a.ctr_id = b.ctr_id 
         INNER JOIN ctr c ON b.ctr_id = c.id 
              WHERE b.ctr_govdept_id = :ctr_govdept_id 
                AND c.soft_deleted < 1
                AND YEAR(a.cashflow_due_date) = :year
              LIMIT 1' );      
$sum = $res->fetch(PDO::FETCH_ASSOC);
if ( ! empty($sum) ) {
    $sumPlanned = $sum["planned_funds_call"];
}
....
Field::inst( 'sub_partner.partner_name as sub_partner.more_partner_names' )->set( false )
    ->getFormatter( function($val, $data, $opts) use ( $db ){
        if ( $data['sub.main_project'] > 0 ) {
            $lnk = getSubProjectIds( $data['ctr.id'], $db );
            $ctrIdStringChildren = implode(", ", array_column($lnk, "linked_ctr_id"));             
            $subProjectString = " AND b.ctr_id IN ( ' . $ctrIdStringChildren .  ' ) ";
            $result = $db->raw()
                ->bind( ':partnerName', $data['sub_partner.partner_name'] )
                ->exec( 'SELECT DISTINCT a.partner_name
                           FROM sub_partner a 
                     INNER JOIN sub b ON b.sub_partner_id = a.id 
                          WHERE a.partner_name <> :partnerName ' 
                                .  $subProjectString . ' 
                       ORDER BY 1' );      
            $partners = $result->fetchAll(PDO::FETCH_ASSOC);
            if ( ! empty($partners) ) {
                return "<br>( " . implode("; ", array_column($partners, "partner_name")) . " )";
            }
        }
        return "";
    }),

co-operate

Thank you, that's great!