Link Table State Field Values Being Reset Using Mjoin

tscully

Hello,

I have what I think is a rather unique problem I'm trying to solve. I have a set of tables of business locations, a table of predefined amenitys that can be at any location, and tables for predefined statuss and notes that each amenity can have, per each association with a location.

I'm using a link table to make the associations between each location and each amenity at that location, along with the status and note state for that amenity "instance".

Here's what that looks like:

location_amenity

id |location_id|amenity_id|status_id|note_id
---+-----------+----------+---------+-------
  1|          1|         2|        1|      1
  2|          2|         3|        3|      3
  3|          3|         1|        2|      8
  4|          4|         2|        1|      6
  5|          5|         3|        4|      1
  6|          6|         1|        1|      2
  7|          7|         2|        1|      1
  8|          8|         2|        2|      4
  9|          9|         1|        1|      2

I'm using an Mjoin to manage the associations, like so:

Editor::inst($db, "location")
    ->fields(
        Field::inst("location.id"),
        Field::inst("location.title")
            ->validator(Validate::required()),
        Field::inst("location.address")
            ->validator(Validate::required()),
    )
    ->join(
        Mjoin::inst("amenity")
            ->link("location.id", "location_amenity.location_id")
            ->link("amenity.id", "location_amenity.amenity_id")
            ->fields(
                Field::inst("id"),
                Field::inst("title")
            )
    )
    ->process($_POST)
    ->json();

The issue comes when a new amenity is added to a location, the status_id and note_id fields get reset on existing location amenitys because (as mentioned in the docs), the association rows are deleted, then recreated whenever a change is requested for the given location.

The only ways I can think of to prevent this from happening is to either:
1. Restructure my data/tables another way that I haven't been able to wrap my mind around yet
2. Somehow intercept the Mjoin operation, record the existing status_id and note_id values, then tell DataTables to use those values in the newly created association rows.

So far I'm at a loss as to how to go about either of these options, but I was wondering if anyone here could provide any wisdom.

Thanks so much!
-Tim.

rf1234

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

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

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.