Is it possible to manage linked tables contains Date validity

support armony

Hello,
I'm already use Editor (with PHP server-side) for simple table and it's work fine.
Now I have a structured database with some linked tables.

Table DEVICE
+-------------------------+--------------+------+-----+---------+-------+
| Field                        | Type         | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+-------+
| DEVICE_ID               | INT            | NO   | PRI |            |          |
+-------------------------+--------------+------+-----+---------+-------+
| DEVICE_NAME        | VARCHAR | NO   |        |            |          |
+-------------------------+--------------+------+-----+---------+-------+

Table AREA
+-------------------------+--------------+------+-----+---------+-------+
| Field                        | Type         | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+-------+
| AREA_ID                  | INT            | NO   | PRI |            |          |
+-------------------------+--------------+------+-----+---------+-------+
| AREA_NAME           | VARCHAR | NO   |        |            |          |
+-------------------------+--------------+------+-----+---------+-------+

Table DEVICE_AREA
+-------------------------+--------------+------+-----+---------+-------+
| Field                        | Type         | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+-------+
| DEVICE_ID              | INT            | NO   |         |            |          |
+-------------------------+--------------+------+-----+---------+-------+
| AREA_ID                  | INT            | NO   |        |            |          |
+-------------------------+--------------+------+-----+---------+-------+
| START_DATE          | DATETIME | NO   |        |            |          |
+-------------------------+--------------+------+-----+---------+-------+
| END_DATE              | DATETIME | NO   |        |            |          |
+-------------------------+--------------+------+-----+---------+-------+

When a DEVICE change his AREA, we should :
* update the existing line in DEVICE_AREA with an END_DATE
* insert a new line IN DEVICE_AREA with a START_DATE, a DEVICE_ID and an AREA_ID

Is it possible to do that with Editor and MJoin?

rf1234

answer is no

but it doesn't mean it can't be done!
- make new table current_device_area with just two cols device_id and area_id
- copy all current records from device_area to current_device_area (just the tow id fields)
- do the Mjoin on current_device_area
- on "postEdit", "postCreate" and maybe "postRemove" do the manipulation of device_area based on the changes you get passed into the event handler using proprietary SQL (alternatively you can also use "writeEdit" etc.)

https://editor.datatables.net/manual/php/events

Here is an example for database manipulations on "writeEdit". Anything is possible!
You can use the ->insert, ->update etc. methods or the more flexible ->raw() method which is commented in this code example but works as well.

->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['id']             
        ), array( 'id' => $delId ) );
//            $db->raw()
//               ->bind( ':softDeleted',  1 ) 
//               ->bind( ':creatorId',    $_SESSION['id'] )
//               ->bind( ':fileId',       $delId )
//               ->exec( 'UPDATE `file` 
//                           SET `soft_deleted`  = :softDeleted,
//                               `creator_id`    = :creatorId 
//                         WHERE `id`            = :fileId' );
    }
    $_SESSION['deletedFiles'] = [];
} )

support armony

Hi,
Finally I found a solution.

First I need to add an ID to my linked table DEVICE_AREA.

Table DEVICE
+-------------------------+--------------+------+-----+---------+-------+
| Field                        | Type         | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+-------+
| DEVICE_ID               | INT            | NO   | PRI |            |          |
+-------------------------+--------------+------+-----+---------+-------+
| DEVICE_NAME        | VARCHAR | NO   |        |            |          |
+-------------------------+--------------+------+-----+---------+-------+
 
Table AREA
+-------------------------+--------------+------+-----+---------+-------+
| Field                        | Type         | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+-------+
| AREA_ID                  | INT            | NO   | PRI |            |          |
+-------------------------+--------------+------+-----+---------+-------+
| AREA_NAME           | VARCHAR | NO   |        |            |          |
+-------------------------+--------------+------+-----+---------+-------+
 
Table DEVICE_AREA
+-------------------------+--------------+------+-----+---------+-------+
| Field                        | Type         | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+-------+
| DEVICE_AREA_ID   | INT           | NO    | PRI |             |          |
+-------------------------+--------------+------+-----+---------+-------+
| DEVICE_ID              | INT            | NO   |         |            |          |
+-------------------------+--------------+------+-----+---------+-------+
| AREA_ID                  | INT            | NO   |        |            |          |
+-------------------------+--------------+------+-----+---------+-------+
| START_DATE          | DATETIME | NO   |        |            |          |
+-------------------------+--------------+------+-----+---------+-------+
| END_DATE              | DATETIME | NO   |        |            |          |
+-------------------------+--------------+------+-----+---------+-------+

Then in my server-side page in PHP I catch the event 'postEdit' to call a function

->on ('postEdit',function($editor,$id,$values){
        AreaChange($editor->db(),'postEdit',$id,$values);
    })

Finally my function make 2 sql order : an update to the actual value (to pass the END_DATE) and an insert to add the new value

function AreaChange($db,$action,$id,$values){
    
    $jsonvalues = json_encode( $values );
    $jsondecode = json_decode($jsonvalues);

    $DEVICE_ID = $jsondecode->{'tb_cons_ce_g4_device'}->{'DEVICE_ID'};
    $DEVEUI = $jsondecode->{'tb_cons_ce_g4_device'}->{'DevEUI'};
    $AREA_DEVICE_ID = $jsondecode->{'AREADEVICE'}->{'AREA_DEVICE_ID'};
    $END_DATE = $jsondecode->{'AREADEVICE'}->{'END_DATE'};
    $NEW_AREA_ID = $jsondecode->{'NEW_AREA_ID'};
    
    //Mise à jour du lien actuel (Date de fin = date de changement)
    $db->update('tb_cons_ce_area_device_id',
        array(
            'END_DATE' => $END_DATE         
        ),
        array(
            'AREA_DEVICE_ID' => $AREA_DEVICE_ID
        )
    );

    //Nouveau lien (avec Date de début = date de changement)
    $db->insert('tb_cons_ce_area_device_id',
        array(
            'AREA_ID' => $NEW_AREA_ID,
            'DEVICE_ID' => $DEVICE_ID,
            'START_DATE'    => $END_DATE            
        )
    );
}

There are still things to improve (like Validator and optimize the code) but the idea is here and it's works for me.

colin

Glad to hear, thanks for reporting back,

Colin