Simple left join not populating select field

jonescw

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:  ",
            name: "ReportGroup.reportGroupID",
            type: "readonly",
            attr: {
               placeholder: "System generated ID number (Readonly)",
            }
         }, {
            label: "GROUP NAME:  ",
            name: "ReportGroup.reportGroupName"
         }, {
          label: "ROLE FEATURE NAME:  ",
          name: "ReportGroup.reportGrouproleID",
            type: "select"
       }, {
            label: "DESCRIPTION:  ", 
            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

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

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

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