Prevent row deletion on joined tables

Prevent row deletion on joined tables

jbronikowskijbronikowski Posts: 7Questions: 4Answers: 0

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();

Answers

  • allanallan Posts: 43,132Questions: 1Answers: 5,608 Site admin

    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

  • jbronikowskijbronikowski Posts: 7Questions: 4Answers: 0
    {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 )"
    
  • allanallan Posts: 43,132Questions: 1Answers: 5,608 Site admin

    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

  • jbronikowskijbronikowski Posts: 7Questions: 4Answers: 0

    /*! 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)); 
    
  • allanallan Posts: 43,132Questions: 1Answers: 5,608 Site admin

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

    Thanks,
    Allan

Sign In or Register to comment.