Prevent row deletion on joined tables

jbronikowski

How do you prevent rows from deleting from a left join table when you delete a row? I would like to delete a row from sppScheduleNetwork but not Site.

Editor::inst( $db, 'sppScheduleNetwork', 'siteID' )
->debug( true )
    ->fields(
        Field::inst( 'sppScheduleNetwork.siteID' )
            ->validator( 'Validate::numeric' ),
        Field::inst( 'sppScheduleNetwork.notes' ),
        Field::inst( 'sppScheduleNetwork.tenativeActivationDate' )
            ->validator( 'Validate::dateFormat', array( 'format'=>'m/d/y' ) )
            ->getFormatter( 'Format::date_sql_to_format', 'm/d/y' )
            ->setFormatter( 'Format::date_format_to_sql', 'm/d/y' ),    
        Field::inst( 'sppScheduleNetwork.confirmedActivationDate' )
            ->validator( 'Validate::dateFormat', array( 'format'=>'m/d/y' ) )
            ->getFormatter( 'Format::date_sql_to_format', 'm/d/y' )
            ->setFormatter( 'Format::date_format_to_sql', 'm/d/y' ),
        Field::inst( 'sppScheduleNetwork.actualActivationDate' )
            ->validator( 'Validate::dateFormat', array( 'format'=>'m/d/y' ) )
            ->getFormatter( 'Format::date_sql_to_format', 'm/d/y' )
            ->setFormatter( 'Format::date_format_to_sql', 'm/d/y' ),
        Field::inst( 'Site.site_name' ),
        Field::inst( 'Site.region' ),
        Field::inst( 'Site.site_city' )
        
    )
    ->leftJoin( 'Site', 'sppScheduleNetwork.siteID', '=', 'Site.site_id' )
    ->process( $_POST )
    ->json();

allan

Editor shouldn't be deleting the left joined table at all. Can you show me the JSON being returned from the server after a delete action? Is it possible the database has ON CASCADE DELETE for that reference?

Allan

jbronikowski

{data: [], debugSql: [{query: "DELETE FROM `Site` WHERE (`Site`.`site_id` = :where_1 )",…},…]}
data
:
[]
debugSql
:
[{query: "DELETE FROM `Site` WHERE (`Site`.`site_id` = :where_1 )",…},…]
0
:
{query: "DELETE FROM `Site` WHERE (`Site`.`site_id` = :where_1 )",…}
bindings
:
[{name: ":where_1", value: "122", type: null}]
query
:
"DELETE FROM  `Site` WHERE (`Site`.`site_id` = :where_1 )"
1
:
{query: "DELETE FROM `sppScheduleVoice` WHERE (`sppScheduleVoice`.`siteID` = :where_1 )",…}
bindings
:
[{name: ":where_1", value: "122", type: null}]
query
:
"DELETE FROM  `sppScheduleVoice` WHERE (`sppScheduleVoice`.`siteID` = :where_1 )"

allan

That does indeed show that Editor is attempting to delete from the joined table. That's very odd, since it doesn't do that here.

Can you show me your full Javascript as well please?

What version of Editor are you using on the client and server-side?

Allan

jbronikowski

/*! DataTables Editor v1.6.3
*
* ©2012-2017 SpryMedia Ltd, all rights reserved.
* License: editor.datatables.net/license
*/

/**
* @summary DataTables Editor
* @description Table editing library for DataTables
* @version 1.6.3
* @file dataTables.editor.js
* @author SpryMedia Ltd
* @contact www.datatables.net/contact
*/

(function($){

$(document).ready(function() {
  var editor = new $.fn.dataTable.Editor( {
    ajax: '/DataTablesEditor/php/table.sppScheduleVoice.php',
    table: '#sppScheduleVoice',
    fields: [
      {
        "label": "Site ID:",
        "name": "sppScheduleVoice.siteID"
      },      
      {
        "label": "Estimated Install:",
        "name": "sppScheduleVoice.tenativeActivationDate",
        "type": "datetime",
        "format": "MM\/DD\/YY"
      },
      {
        "label": "Confirmed Install:",
        "name": "sppScheduleVoice.confirmedActivationDate",
        "type": "datetime",
        "format": "MM\/DD\/YY"
      },
      {
        "label": "Actual Install:",
        "name": "sppScheduleVoice.actualActivationDate",
        "type": "datetime",
        "format": "MM\/DD\/YY"
      },
      {
        "label": "Planned Ports:",
        "name": "sppScheduleVoice.plannedPorts"
      },
      {
        "label": "Actual Ports:",
        "name": "sppScheduleVoice.actualPorts"
      },
      {
        "label": "Notes:",
        "name": "sppScheduleVoice.notes"
      },
    ]
  } );


  var table = $('#sppScheduleVoice').DataTable( {
    dom: 'Bfrtip',
    ajax: '/DataTablesEditor/php/table.sppScheduleVoice.php',
    columns: [
      {
        "data": "sppScheduleVoice.siteID"
      },
      {
        "data": "Site.site_name"
      },
      {
        "data": "Site.region"
      },
      {
        "data": "sppScheduleVoice.tenativeActivationDate"
      },
      {
        "data": "sppScheduleVoice.confirmedActivationDate"
      },
      {
        "data": "sppScheduleVoice.actualActivationDate"
      },
      {
        "data": "sppScheduleVoice.plannedPorts"
      },
      {
        "data": "sppScheduleVoice.actualPorts"
      },
      {
        "data": "sppScheduleVoice.notes"
      },
      
    ],
    select: true,
    lengthChange: false,
    stateSave: true,
    "paging":   false,
        "info":     false,
    buttons: [
      { extend: 'create', editor: editor },
      { extend: 'edit',   editor: editor }
      { extend: 'remove',   editor: editor }
    ]
  } );


} );



}(jQuery)); 

allan

Could you try upgrading to 1.6.5 on both the client and server-side please?

Thanks,
Allan

Mariusz Glugla

Hi
Was this problem fixed?
I have just discovered same using Oracle.
When joined two tables having same key_field, Editor deletes rows from both tables during DELETE.

Editor::inst( $db, 'WI_PORTAL_STAFF', 'USER_ID') )
    ->fields(
        Field::inst( 'WI_PORTAL_STAFF.USER_ID' )
            ->options( Options::inst()
                ->table( 'WI_USERS' )
                ->value( 'USER_ID' )
                ->label( 'NAME' )
            )
            ->validator( Validate::dbValues() )
        ,Field::inst( 'WI_USERS.NAME' )
    )
    ->leftJoin( 'WI_USERS', 'WI_USERS.USER_ID', '=', 'WI_PORTAL_STAFF.USER_ID' )

Problem disappeared when I created complex key adding filed which is not in other table (not sure if it does matter, but I decided to mention it).

Editor::inst( $db, 'WI_PORTAL_STAFF', array('USER_ID', 'TICKET_NR')) )

allan

Could you add ->debug( true ) immediately before the ->process( ... ) call please? Then show me the JSON return from the server when you delete a row. It will contain the SQL statements used by Editor.

Thanks,
Allan

Mariusz Glugla

Alan:

{"data":[],"debug":[{"query":"DELETE FROM  \"WI_USERS\" WHERE (\"WI_USERS\".\"USER_ID\" = :where_1 )","bindings":[{"name":":where_1","value":"USER1","type":null}]},{"query":"DELETE FROM  \"WI_PORTAL_STAFF\" WHERE (\"WI_PORTAL_STAFF\".\"USER_ID\" = :where_1 )","bindings":[{"name":":where_1","value":"USER1","type":null}]}]}

allan

Thanks. Could you confirm what version of the PHP libraries you are using please? I've just tried the exact above code with 1.7.3 and it appears to function as expected.

Thanks,
Allan

allan

Ignore that - sorry. I've just got it working (i.e. in the broken state). Its because I was throwing an error when executing against a table I don't have. I'll update hwere when I have this addressed.

Allan

allan

It appears that the code is actually working as designed at the moment - it might be that the design has a bug though... What it is doing is seeing that you have a row in your WI_USERS table that is dependent upon the value in the WI_PORTAL_STAFF table.

It is incorrectly assuming that if you delete the entry from the staff table then you would also want to delete it from the users table (since if it left the number in place in the users table it would break referential integrity).

What is your intention for the WI_USERS.USER_ID value when you delete from the WI_PORTAL_STAFF table? Do you want it to be set to null rather than being deleted?

Thanks,
Allan

Mariusz Glugla

I would say assumption is wrong. Joined table is only a helper.
In my case I want to display/edit User from WI_PORTAL_STAFF using WI_USERS for displaying only friendly name. In DB it can be WI_PORTAL_STAFF having foreign key on USER_ID pointing to USER_ID in WI_USERS. If I would like to have associated row deleted from WI_PORTL_STAFF upon deleting from WI_USERS table, I would define foreign key with option ON DELETE CASCADE, but this is DB architect to decide what to do with orphaned rows, DataTables do not have to worry about it.

allan

Agreed - I think this should indeed fall upon the database schema rather than attempting to do it in the PHP code.

For the moment, if you open the Editor.php file and search for the comment // Remove from the left join tables - comment out that entire for loop and it will operate as expected.

I've logged this as something to be changed.

Thanks,
Allan

Mariusz Glugla

Thank you Allan.