Answers

• rf1234 Posts: 2,996Questions: 87Answers: 421

  December 2023 edited December 2023

  Hi Tim,
  I was fiddling around a little with an E/R diagram but I couldn't really make it work because

  predefined statuss and notes that each amenity can have, per each association with a location.

  "per each association with a location" makes it difficult. You would need link tables with more than two columns which doesn't work as far as I know.

  You could use Editor event handlers to
  - save the two values in Session Variables (if you use PHP for example)
  - update the link table with the saved values after execution of the Editor update with the mjoin.

  The other alternative is you get rid of the mjoin by putting focus on your link table: That table then would have to be the table that is edited primarily. The other tables are just left joined. I have been doing this from to time in cases like yours. But this can be quite tricky ...

  Here are the event handlers for my first suggestion:
  https://editor.datatables.net/manual/php/events

  You could use "validatedCreate" and "validatedEdit" to save the values that will be lost unless you send them to the server anyway. And then "writeCreate" and "writeEdit" to update the link table with those values again.

  Here is some sample code from my own coding showing an UPDATE statement using Editor's "raw" method. I am using some of the values submitted to the server by Editor but also values that I posted to the server plus the Session ID.

  ->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
          $db->raw()
             ->bind( ':vat_subcategory_id',   $values['vat_result']['vat_subcategory_id'] )     
             ->bind( ':vat_result',           $values['vat_result']['vat_result'] )   
             ->bind( ':vat_hint',             $values['vat_result']['vat_hint'] )     
             ->bind( ':updater_id',           $_SESSION['orig_id'] )
             ->bind( ':ctr_id',               $_POST['ctr_id'] ) 
             ->bind( ':vat_assessment_type',  $_POST['vatAssessmentType'] )
             ->exec( 'UPDATE vat_result
                         SET vat_subcategory_id   = :vat_subcategory_id,
                             vat_result           = :vat_result,
                             vat_hint             = :vat_hint,
                             updater_id           = :updater_id
                       WHERE ctr_id               = :ctr_id
                         AND vat_assessment_type  = :vat_assessment_type' );
          })
  

  Here is where the two $_POST variable get posted from the client. You might be able to use this to post the values that are missing later on after the Mjoin.

  var ctrPSCEditor = new $.fn.dataTable.Editor({
      ajax: {
          url: 'actions.php?action=tblPSC',
          data: function ( d ) {
              d.ctr_id = parentId;
              // 0: "vat", 1: "preTax" or 2: "preTaxCorrection"
              d.vatAssessmentType = vatAssessmentType;
          }
      },
      table: "#tblPSC",
  

  Here is another one that emulates an Mjoin in a read-only situation where the "real" Mjoin wouldn't work also using the "raw" method.

  Field::inst( 'ctr.id AS ctr_has_ctr' )->set( false )   //return same format as an MJoin             
      ->getFormatter( function($val, $data, $opts) use ( $db ){
          $stmt = ('SELECT b.linked_ctr_id, a.serial, c.sub_name 
                      FROM ctr a  
                INNER JOIN ctr_has_ctr b ON a.id = b.linked_ctr_id 
                INNER JOIN sub c         ON a.id = c.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);
      } ),
  

  To select a single value you would only need to replace "fetchAll" with "fetch" like in here:

  Field::inst( 'vat_subcategory.user_visibility' )
      ->validator( function ( $val, $data, $opts ) use ( $msg, $db ) {
          if ( $val < 1 && $data["vat_subcategory"]["id"] > 0 ) {
              $result = $db->raw()
                  ->bind( ':fk',  $data["vat_subcategory"]["id"] )
                  ->exec( 'SELECT COUNT(*) AS resultCount 
                             FROM vat_result  
                            WHERE vat_subcategory_id = :fk' );
              $row = $result->fetch(PDO::FETCH_ASSOC);
              if ( (bool)$row["resultCount"] ) {
                  return $msg[4];
              }
          }
          return true;
      } ),
  

  Good luck!
  Roland
• rf1234 Posts: 2,996Questions: 87Answers: 421

  December 2023

  Forgot to mention that Editor also has its own methods for INSERT and UPDATE.

  Here is an example which is part of my solution to implement soft deletes of contracts and files so that nothing ever gets deleted physically.

  ->on( 'writeEdit', function ( $editor, $id, $values ) use ( $db ) {
  // we reinsert the deleted link table record and update the file table
  // entry to be soft_deleted
      foreach ( $_SESSION['deletedFiles'] as $delId ) {            
          //re-insert link table between ctr and file
          $res = $db->insert( 'ctr_has_file', array (
              'ctr_id'    => $id,
              'file_id'   => $delId
          ) );
  
          // update the file record to be soft_deleted
          $res = $db->update( 'file', array( 
              'soft_deleted'  => 1,
              'creator_id'    => $_SESSION['orig_id']             
          ), array( 'id' => $delId ) );
      }
      $_SESSION['deletedFiles'] = [];
  } )
  

  I also found the Editor SELECT method. Never used that one before

  Editor::inst( $db, 'users' )
      ->field( 
          Field::inst( 'users.first_name' ),
          Field::inst( 'users.last_name' ),
          Field::inst( 'users.phone' ),
          Field::inst( 'users.site' )
              ->options( function($db) {
                  return $db->select('sites', array('id', 'name', 'continent'))->fetchAll();
              } )
              ->validator( Validate::dbValues(null, 'id', 'sites') ),
          Field::inst( 'sites.name' )
      )
      ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
      ->process($_POST)
      ->json();
  
• tscully Posts: 6Questions: 3Answers: 0

  January 2

  You've given me a lot of good stuff to sift through, thank you Roland! I'll tinker with these and report back.

  Thanks again!
  -Tim.