Simple left join not populating select field

jonescw · September 2015

Simple left join with select field. In bubble the select field is empty, In inline I get error 11. I have read error 11 and add the editField but still does not work.

Using editor 1.5.1

SQL
SELECT ReportGroup.reportGroupID, ReportGroup.reportGroupName, ReportGroup.reportGroupDescr,
MBRROLE_FEATURE.ROLEFEATURE_ID, MBRROLE_FEATURE.NAME
FROM ReportGroup as ReportGroup
LEFT JOIN MBRROLE_FEATURE
ON ReportGroup.reportGroupRoleID = MBRROLE_FEATURE.ROLEFEATURE_ID

HTML

<table id="reportGroupedit" class="display" cellspacing="0" width="100%">
   <thead>
      <tr>
           <th></th>
           <th>GROUP ID</th>  
           <th>GROUP NAME</th>
           <th>ROLE</th>
           <th>DESCRIPTION</th>  
       </tr>
    </thead>
</table>

   $('#reportGroupedit').DataTable( {
      "paging":         false, 
      "scrollCollapse": true, 
      "scrollY":        "20em",
      "ScrollX":        "100%",
      dom:              "BfTrtip", 
      ajax:             "/../../login/mysql/crudReportGroup.php",
      columns: [
         {  data: null, 
            defaultContent: '', 
            className: 'select-checkbox',
            orderable: false 
         }, 
         { data: "ReportGroup.reportGroupID" },
         { data: "ReportGroup.reportGroupName" },
         { data: "MBRROLE_FEATURE.NAME", editField: 'MBRROLE_FEATURE.ROLEFEATURE_ID'},
         { data: "ReportGroup.reportGroupDescr" } 
      ],
      order: [ 1, 'asc' ],
      select: {
            style:    'os',
            selector: 'td:first-child'
      },
      buttons: [
            { extend: "create", editor: reportGroup_Editor },
            { extend: "edit",   editor: reportGroup_Editor },
            { extend: "remove", editor: reportGroup_Editor }
         ]
   });

   reportGroup_Editor = new $.fn.dataTable.Editor( {
      ajax: {
         url: "/../../login/mysql/crudReportGroup.php",
      },
      table: "#reportGroupedit",  //HTML table not sql table
      fields: [ {
            label: "GROUP ID:&nbsp;&nbsp;",
            name: "ReportGroup.reportGroupID",
            type: "readonly",
            attr: {
               placeholder: "System generated ID number (Readonly)",
            }
         }, {
            label: "GROUP NAME:&nbsp;&nbsp;",
            name: "ReportGroup.reportGroupName"
         }, {
          label: "ROLE FEATURE NAME:&nbsp;&nbsp;",
          name: "ReportGroup.reportGrouproleID",
            type: "select"
       }, {
            label: "DESCRIPTION:&nbsp;&nbsp;", 
            name: "ReportGroup.reportGroupDescr",
            type: "textarea"
         }
      ],
      i18n: {
            create: {
                title:  "Create New Role Feature",
            },
            edit: {
                title:  "Edit Role Feature",
            },
            remove: {
                title:  "Delete Role",
                confirm: {
                    1: "Are you sure you wish to delete the checked Feature?",
                }
             }
         },
      display: 'jqueryui',
      formOptions: {
         main: {
            onReturn:   'none'
         }
      }
   });

PHP

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\join,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;


   Editor::inst($db) 
   ->table("ReportGroup")
   ->pkey('reportGroupID')
   ->fields(
         Field::inst( 'ReportGroup.reportGroupID' ),
         Field::inst( 'ReportGroup.reportGroupName' )->validator( 'Validate::notEmpty' ),
         Field::inst( 'ReportGroup.reportGroupDescr' ),
         Field::inst( 'ReportGroup.reportGroupRoleID')
            ->options('MBRROLE_FEATURE', 'MBRROLE_FEATURE.ROLEFEATURE_ID', 'MBRROLE_FEATURE.NAME'),
         Field::inst( 'MBRROLE_FEATURE.NAME')
     )
   ->leftJoin("MBRROLE_FEATURE",'ReportGroup.reportGroupRoleID','=', 'MBRROLE_FEATURE.ROLEFEATURE_ID')

   ->process( $_POST )
   ->json();

AJAX load
{
"data":[
{
"DT_RowId":"row_1",
"ReportGroup":
{
"reportGroupID":"1",
"reportGroupName":"Admin",
"reportGroupDescr":"",
"reportGroupRoleID":"101"
},
"MBRROLE_FEATURE":{
"NAME":"RPTGRP_ADMIN"
}
},
{
"DT_RowId":"row_2",
...

I understand I have a syntax error but I can not find it. Any help would be-helpful

allan · September 2015

I think you actually want your column definition to be:

{ data: "MBRROLE_FEATURE.NAME", editField: 'ReportGroup.reportGrouproleID'},

i.e. set editField to be the name of the field that you want to edit the value of.

Allan

jonescw · September 2015

The change fixed the inline editing.

I fixed the empty select list. Missed spelled field name it should be ReportGroup.reportGroupRoleID and I had ReportGroup.reportGrouproleID.

thanks for your help

jonescw · September 2015

okay got the webpage to look correct but now none of the edit command works

New - does not create a new record
Edit - does not change anything

No insert or update SQL statement in but into SQL log I have running (file_put_contents( '/home/benjiw/tmp/b', $sql."\n", FILE_APPEND );

New console.log

e: 
j…y.Event {type: "preSubmit", timeStamp: 1442949053480, jQuery111306478835251182318: true, isTrigger: 2, namespace: ""…}
 json: 
 {
    "action": "create",
    "data": {
        "0": {
            "ReportGroup": {
                "reportGroupID": "",
                "reportGroupName": "yyyyyyyyyyyyyyyy",
                "reportGroupRoleID": "4",
                "reportGroupDescr": "yyyyyyyyyyyyyyyyyyy"
            }
        }
    }
}
 data: 
 "create"
 1

edit console.log

j…y.Event {type: "initEdit", timeStamp: 1442948826386, jQuery11130013774991035461426: true, isTrigger: 2, namespace: ""…}
adminReports.php:845 {
    "_DT_RowIndex": 4,
    "sizzle1442948819503": {
        "parentNode": [
            50,
            36,
            true
        ]
    }
}
{
    "DT_RowId": "row_5",
    "ReportGroup": {
        "reportGroupID": "5",
        "reportGroupName": "dddddd",
        "reportGroupDescr": "dddddddd",
        "reportGroupRoleID": "102"
    },
    "MBRROLE_FEATURE": {
        "NAME": "5"
    }
}
[4, context: Array[1], selector: Object, ajax: Object]
report group preCreate
e: 
j…y.Event {type: "preSubmit", timeStamp: 1442948834021, jQuery11130013774991035461426: true, isTrigger: 2, namespace: ""…}
json: 
{
    "action": "edit",
    "data": {
        "row_5": {
            "ReportGroup": {
                "reportGroupID": "5",
                "reportGroupName": "dddddd",
                "reportGroupRoleID": "10",
                "reportGroupDescr": "dddddddd"
            }
        }
    }
}
data: 
"edit"
1

e: 
j…y.Event {type: "submitSuccess", timeStamp: 1442948834633, jQuery11130013774991035461426: true, isTrigger: 2, namespace: ""…}
json: 
{
    "data": [
        {
            "DT_RowId": "row_5",
            "ReportGroup": {
                "reportGroupID": "5",
                "reportGroupName": "dddddd",
                "reportGroupDescr": "dddddddd",
                "reportGroupRoleID": "102"
            },
            "MBRROLE_FEATURE": {
                "NAME": "5"
            }
        }
    ],
    "error": "",
    "fieldErrors": []
}
data: 
{
    "DT_RowId": "row_5",
    "ReportGroup": {
        "reportGroupID": "5",
        "reportGroupName": "dddddd",
        "reportGroupDescr": "dddddddd",
        "reportGroupRoleID": "102"
    },
    "MBRROLE_FEATURE": {
        "NAME": "5"
    }
}
1

Simple left join not populating select field

Simple left join not populating select field

Answers

Howdy, Stranger!

Categories

DataTables

Simple left join not populating select field

Simple left join not populating select field

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables