MySQL Update Sum of Child Quantity in Parent Table

cpshart

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

I have created a parent child setup similar to your blog

https://datatables.net/blog/2019-01-11

I have two tables for parent and child dm_holdings & dm_holdinglines respectively.

If a parent has no child rows the holdings quantity is held at parent level dm_holdings.quantity. As soon as child rows are added to a parent row, I want the quantity of the parent to be updated to the summated quantity of all associated child rows.quantity

Child Rows Exist, Yes
dm_holdings.quantity = summation of dm_holdinglines.quantity
Child Rows Exist, No
dm_holdings.quantity = original edited value.

Problem is as follows

I need to update my parent table dm_holdings.quantity to summation of dm_holdinglines.quantity, where child rows exists given by ( data.length === 0 ) is FALSE in code below

The value of dm_holdings.quantity is being updated correctly in the datatable as shown in the screen shot, but I need to update the value in the MySQL Table dm_holdings.quantity.
i.e. If I Edit the selected parent table it will show the original edited quantity, not the summated value displayed on the datatable.

I can provide access to my system if required to investigate further. I have seen update examples on the forum where buttons are used, but I need this update to work in response to any change in the child rows, delete, edit, new, duplicate.

This command runs the script snippet

https://www.dividendlook.co.uk/stock-holdings-u/

Client Extract Script Snippet
https://www.dividendlook.co.uk/wp-admin/post.php?post=25706&action=edit

            { data: 'dm_holdinglines',
              render: function (data, type, row) {
                if ( data.length === 0 ) {
                  return row.dm_holdings.quantity;
                } else {

                return data.reduce( function (accum, item) {
                    // check if Buy transaction type, or Sell
                    
                    return (item.transaction_type == 'Buy')
                  ? ( parseFloat(accum) + parseFloat(item.quantity) ).toFixed(4)// Buy
                  : ( parseFloat(accum) + parseFloat(item.quantity) ).toFixed(4)// Sell                 
                }, 0 ); 
        
                    }
              }
            },                                  
            { data: "dm_stocks.price" }, 

Server Script Extract
Editor-PHP-1.9.4/controllers/ht.php

    )
    ->join(
        Mjoin::inst( 'dm_holdinglines' )
            ->link( 'dm_holdings.id', 'dm_holdinglines.holdings_id' )
            ->fields(
                Field::inst( 'id' ),
                Field::inst( 'quantity' ),
                Field::inst( 'transaction_type' )
            )
    )

Many Thanks

Colin

rf1234

i.e. If I Edit the selected parent table it will show the original edited quantity, not the summated value displayed on the datatable.

That should be no problem: In your Editor you simply need to show the original value which you don't show in the data table. If "it" doesn't mean Editor but the Data Table then you would need to have a field in your database that saves whether or not a value was edited. Depending on that you would use a renderer client side to decide whether or not to display the summation value or the orginial value.

cpshart

Hi rf1234

Thanks for your quick response, I want to update the column value dm_holdings.quantity to the summated quantity value of child rows where there are child rows.

At the moment the value shown is sourced from dm_holdings.quantity, which is 1451.00, I want the code to update the database table to in this case 7032.00, and update every time there is a change to child row quantity values. So the value in the datatables table should match the value on the parent table dm_holdings.quantity.

My question is what code would I need to add to the snippet to perform this update on an action of edit, delete, duplicate, new.

Many Thanks Colin

rf1234

I think there is a problem with your database design. I wouldn't save the sum of the child records redundantly in the parent database record. That is - let's say - "suboptimal" design. You can always join the child table with the parent table and get this information dynamically. It also saves you from inconsistencies between the parent and the child records once you "forget" an update. That's what relational databases where invented for!

Well, and that is probably the reason why this isn't really supported by Editor and Data Tables. But of course you can do this with one of the Editor server side events if you insist.

On "postEdit", "postCreate" and "postDelete" of the child table you can update the parent table.

Here is something for "postEdit" from my own coding that shows you how to write your proprietary SQL "SELECT" and "UPDATE" statements using Editor's db handler and Editor's raw() method. You would need to update this to select the sum of the child rows and update the parent record accordingly for all of the three events.

$row contains all child table values read back from the server. See the docs here:
https://editor.datatables.net/manual/events

->on( 'postEdit', function ( $editor, $id, $values, $row ) {
    $creditorName = $row['creditor']['name'].', '.$row['creditor']['city'];
    $result = $editor->db()->raw()
        ->bind( ':gov_id', $_SESSION['gov_id'] )  
        ->bind( ':creditor_name', $creditorName )
        ->exec( 'SELECT id  AS  gov_manual_creditor_id 
                   FROM gov_manual_creditor    
                  WHERE gov_id      = :gov_id 
                    AND govdept_id  IS NULL
                    AND name        = :creditor_name
                  LIMIT 1' );
    $gmc = $result->fetch(PDO::FETCH_ASSOC);
    $editor->db()->raw()
       ->bind( ':fk', $gmc["gov_manual_creditor_id"] )
       ->bind( ':id', $id )
       ->exec( 'UPDATE contract   
                   SET gov_manual_creditor_id = :fk, 
                       further_approvals_creditor = 0, 
                       further_approvals_govdept  = 0 
                 WHERE id = :id' );
} )

cpshart

Hi rf1234

Many thanks for a great post, I will investigate both options, joining the child table to the parent table and/or adding similar code to the server file.

I will post back my results.

Best Colin