on postEdit vs ->leftJoin

MarekAdam

Hello
i have logs in my datatables like here: https://datatables.net/forums/discussion/44932/audiot-logging-for-editor

->on( 'preEdit', function ( $editor, $id, $values ) {
     global $prevValues;
     $fields = array_keys(array_shift($values));
     $prevValues = $editor->db()->select( 'weir_ips', $fields, [ 'id' => $id ] )->fetch();
 
 } )
 ->on( 'postEdit', function ( $editor, $id, $values, $row) {
     global $prevValues;
     $values = array_shift($values);
     logChange( 'gcac_log', $editor->db(), 'EDIT', $id, $values, $prevValues );
 } )

The problem is that i use ->leftJoin. The data in $prevValues and $values are without data from joins.
$prevValues i resolve by changing select on sql with joins.

Question
How i can get all data ($values in postEdit) that has changed including those from ->leftJoin

rf1234

The problem is that i use ->leftJoin. The data in $prevValues and $values are without data from joins.

A left join is a condition in an SQL-statement. It doesn't return anything on its own. So what exactly do you mean? And what does the left join look like that you want to emulate on "postEdit"?

If you need to show changes in your log it is sufficient to write only the current state to your log. There are ways to highlight the change versus the previous state.

rf1234

How i can get all data ($values in postEdit) that has changed including those from ->leftJoin

By simply writing everything to your log which I do in here:

->leftJoin( 'fixed_has_cashflow', 'cashflow.id', '=', 'fixed_has_cashflow.cashflow_id')
->leftJoin( 'fixed', 'fixed_has_cashflow.fixed_id', '=', 'fixed.id')                
->leftJoin( 'variable_has_cashflow', 'cashflow.id', '=', 'variable_has_cashflow.cashflow_id')
->leftJoin( 'variable', 'variable_has_cashflow.variable_id', '=', 'variable.id')
->leftJoin( 'cashflow_has_accrual', 'cashflow.id', '=', 'cashflow_has_accrual.cashflow_id')
->leftJoin( 'cashflow_has_derivative_asset_liability', 'cashflow.id', '=', 'cashflow_has_derivative_asset_liability.cashflow_id')       
->where( 'cashflow.contract_id', $_POST['contract_id'] )
->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
    logChange( $editor->db(), 'create', $id, $row, 'cashflow' );
} )
->on( 'postEdit', function ( $editor, $id, $values, $row ) {
    logChange( $editor->db(), 'edit', $id, $row, 'cashflow' );
} )
->on( 'postRemove', function ( $editor, $id, $values ) {
    logChange( $editor->db(), 'delete', $id, $values, 'cashflow' );
} )
->process($_POST)
->json();

Subsequently in your log report which you will need to code as well you can highlight the changes versus the previous state.

MarekAdam

A left join is a condition in an SQL-statement. It doesn't return anything on its own. So what exactly do you mean? And what does the left join look like that you want to emulate on "postEdit"?

If you need to show changes in your log it is sufficient to write only the current state to your log. There are ways to highlight the change versus the previous state.

In the php file that generates data for datatables I have left Join">-> left Join
Datatables is working fine. Logs not. The logs contain only data from the main table. Values from leftjoin are missing.

 ->on( 'postEdit', function ( $editor, $id, $values, $row) {
     global $prevValues;
     $values = array_shift($values);
     logChange( 'gcac_log', $editor->db(), 'EDIT', $id, $values, $prevValues );
 } )

I think array_shift deletes the values from leftjoin outside the main table. When I delete it, data from other tables (from leftjoin) appear in the logs

->on( 'preEdit', function ( $editor, $id, $values ) {
     global $prevValues;
     $fields = array_keys(array_shift($values));
     $prevValues = $editor->db()->select( 'weir_ips', $fields, [ 'id' => $id ] )->fetch();
  
 } )

Can you explain to me how this query can get data as in datatables I had 7 leftJoins?

db()->select( 'weir_ips', $fields, [ 'id' => $id ] )->fetch();

If i delete array_shift i have values like files-many-count or selectable_name-many-count so its not good too.

I left only

  ->on( 'preEdit', function ( $editor, $id, $values ) {
        global $prevValues;
        $prevValues = $values;
    } )

Unfortunately, $values are always POST data, so I have the same in my logs before and after editing. Data must be downloaded from the database in "on preEdit".

How to do it if the php file has more than 100 lines with Field::inst, ->join, ->leftJoin (more than 10 tables)?

Meaby i can use sessions to store data. It's not a good solution, but it's an internal system, there will be 2 users, so probably ok?

rf1234

You don't do it that way, Marek. You just write the data AFTER the change to the log, not the data BEFORE. You already have the data BEFORE the change in the log because that was the previous log that you wrote for the respective record:
1. entry: CREATE, 2nd to nth entry: EDIT, last entry: DELETE. That's it, not before and after!

My log, as posted above, works with left joins. All the fields are there from all of the left joined tables. And that way to do it also complies with @allan's blog post on writing logs.

To your question regarding $db()->select: For more complex queries you can use the ->raw() method like in here:

$statement = ('SELECT DISTINCT a.gov_id AS govId 
                 FROM ctr_installation_has_gov a 
           INNER JOIN ctr_installation b ON a.ctr_installation_id = b.id
           INNER JOIN ctr_govdept_has_ctr_installation c ON b.id = c.ctr_installation_id
           INNER JOIN ctr_govdept d ON c.ctr_govdept_id = d.id
                WHERE d.id = :ctrGovdeptId');  
$result =
$db ->raw()
    ->bind(':ctrGovdeptId', $ctrGovdeptId)
    ->exec($statement);

$row = $result->fetchAll(PDO::FETCH_ASSOC);
foreach ($row as $val) {
    ... do something
}
$statement = ('DELETE a FROM ctr_govdept_has_user a
           INNER JOIN ctr_govdept_has_ctr_installation b 
                        ON a.ctr_govdept_id      = b.ctr_govdept_id 
           INNER JOIN ctr_installation_has_principal c   
                        ON b.ctr_installation_id = c.ctr_installation_id 
                       AND a.user_id             = c.user_id 
                WHERE a.user_id         = :user_id
                  AND c.user_id         = :user_id
                  AND a.ctr_govdept_id  = :ctr_govdept_id');  
$db ->raw()
    ->bind(':user_id',        $userId)
    ->bind(':ctr_govdept_id', $ctrGovdeptId)
    ->exec($statement);

Good luck!
Roland

MarekAdam

I wanted to write edited data on state before edit.
Thanks for info about raw()