Using Delete with Joined tables

[MGA555]

Based on my testing, it appears that when you use the OpenJS Grid delete feature on joined tables, the only record that is deleted is the one in the "From" table, specified in the "$grid = new Grid("table name/", array(" line in the code.

Sometimes that is exactly what you want, other times not. Worse yet, there is no visual indication what will be deleted and what will remain. Years after setting up a grid, who is going to remember what is in which table?

Is there a more involved way to set things up that addresses the optional need to delete the corresponding records in all of the joined tables?.

Another potential trap is that if you display 3 fields in a 10 field table, I assume the entire record of 10 fields is deleted. Is that correct? Any options to make just the 3 fields null (or similar) in the selected record?

[optikalefx]

These aren't common cases in my experience. But it is true that it only deletes from the main table. You can intercept the delete call and then do the delete you want to do. People do that with the save all the time. Because save works the same way, it only will save to that main table.

And yes, it would delete the whole row. Again you can intercept and then null out the row if you want.

[MGA555]

I guess the biggest problem from my point of view is that there is no obvious way for a user to know what they are deleting (or saving). I was hoping with your experience you may have come across a method that could be used to make things clear.to the user.

These issues arise due to the use of the join, which is very common in anything but the most trivial database. Proper database normalization dictates the use of many tables which (when joined for a useful display) will have these issues. Based on my current understanding of OpenJS Grid, I would have to avoid the use of delete and save with joined tables. Do you know how others have addressed these issues?

My level of competency with OpenJS Grid is such that I would have no idea how to intercept and modify a call. Could you provide a simple example to get me started?

thanks

[optikalefx]

I understand where you're coming from. In my years of using OpenJSGrid I've never needed to save off other tables data. Aside from one exception. And for that exception I intercepted the save.

So for me, (which is what I made the grid for) the most common case is handled. You often create a data grid for 1 thing. Sometimes that 1 thing has other associated data, and that's when you intercept the save.

Here is an example of an intercepted save, which is needed to encrypt data before going into the database.

// load the grid class
require_once("packages/grid.php");

// normal grid setup
$grid = new Grid("user", array(
    "delete"=>true,
    "save"=>true,
    "joins"=>array("LEFT JOIN activation ON (user.id = activation.user_id)"),
    "fields"=>array(
        "name"=>"CONCAT(first_name,' ',last_name)",
        "first_name"=>"user.first_name",
        "last_name"=>"user.last_name",
        "activated"=>"activation.used",
        "pass"=>"''"
    ),
    "having"=>"first_name IS NOT NULL && 1=1"
),FALSE);
// note false above. This means don't render until I call render below

// IF we are saving, intercept the save
if(isset($_POST['save']) && $_POST['save'] == "true") {

    // because we intercepted, let's get the data packet that was about to be saved
    $saveArray = $grid->getSaveArray();

        // go through the data packet and do things to it
    foreach($saveArray as $rowId=>$row) {

        // update email
        if(!empty($row['email'])) {
            $uid = $this->_save(array(
                "id"=>$rowId,
                "email"=>$this->_encryptData($row['email']),
                "date_modified"=>"NOW()"
            ),"user");
        }

        // update activated
        if(!empty($row['activated'])) {
            $this->_save(array(
                "user_id"=>$uid,
                "used"=>$row['activated']
            ),"activation","user_id");
        }

        // update free
        $this->_save(array(
            "id"=>$rowId,
            "free"=>$row['free']
        ),"user");
    }

// not saving, just loading
} else {

    // decryption
    foreach($grid->data['rows'] as $key=>$row) {
        $grid->data['rows'][$key]['name'] = $this->_decryptData($row['first_name'])." ".$this->_decryptData($row['last_name']);
        $grid->data['rows'][$key]['email'] = $this->_decryptData($row['email']);
        $grid->data['rows'][$key]['phone'] = $this->_decryptData($row['phone']);
    }

        // manual render since we said FALSE in the main setup
    $grid->render();
}