Data:null column if first return "Unknown field: (index 0)" error

zajc

I'm using Editor 2.0.4. If datatable first column uses data:null I'm getting the following error:

DataTables warning: table id=measuring-points-table - Unknown field: (index 0)

{
className: 'details-control',
orderable: false,
data: null,
defaultContent: '',
width: '10px'
}

If data:null column is not the first column, it works fine.

I don't know what is the problem, it was working fine in 1.9.x.

This is PHP

<?php

include public_path('plugins/editor/lib/DataTables.php');

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst($db, 'measuring_points', 'id')
    ->fields(
        Field::inst('measuring_points.id'),
        Field::inst('measuring_points.location_id')
            ->options('locations', 'id', 'name')
            ->validator(Validate::required()),
        Field::inst('locations.name'),
        Field::inst('measuring_points.device_id')
            ->options('devices', 'id', 'name')
            ->validator(Validate::required()),
        Field::inst('devices.name'),
        Field::inst('measuring_points.notes'),
        Field::inst('measuring_points.created_at')
            ->set(Field::SET_CREATE)
            ->setValue(date("Y-m-d H:i:s"))
            ->getFormatter(Format::date_sql_to_format('d/m/Y H:i:s')),
        Field::inst('measuring_points.updated_at')
            ->set(Field::SET_EDIT)
            ->setValue(date("Y-m-d H:i:s"))
            ->getFormatter(Format::date_sql_to_format('d/m/Y H:i:s'))
    )
    ->leftJoin('locations', 'locations.id', '=', 'measuring_points.location_id')
    ->leftJoin('devices', 'devices.id', '=', 'measuring_points.device_id')
    ->debug(true)
    ->process($_POST)
    ->json();

JSON returned is

{
   "data":[
      {
         "DT_RowId":"row_1",
         "measuring_points":{
            "id":"1",
            "location_id":"3",
            "device_id":"2",
            "notes":null,
            "created_at":"17\/04\/2021 16:36:38",
            "updated_at":"07\/05\/2021 12:50:05"
         },
         "locations":{
            "name":"Location2"
         },
         "devices":{
            "name":"M676"
         }
      },
      {
         "DT_RowId":"row_2",
         "measuring_points":{
            "id":"2",
            "location_id":"2",
            "device_id":"2",
            "notes":null,
            "created_at":"18\/04\/2021 10:08:58",
            "updated_at":"18\/04\/2021 10:08:58"
         },
         "locations":{
            "name":"Location1"
         },
         "devices":{
            "name":"M676"
         }
      }
   ],
   "options":{
      "measuring_points.location_id":[
         {
            "label":"L457",
            "value":"5"
         },
         {
            "label":"Location1",
            "value":"2"
         },
         {
            "label":"Location2",
            "value":"3"
         },
         {
            "label":"Location3",
            "value":"4"
         },
         {
            "label":"Room 1",
            "value":"6"
         }
      ],
      "measuring_points.device_id":[
         {
            "label":"M676",
            "value":"2"
         },
         {
            "label":"TH54-56",
            "value":"1"
         },
         {
            "label":"test 999",
            "value":"4"
         }
      ]
   },
   "files":[

   ]
}

Datatable is

        var table = $('#measuring-points-table').DataTable({
            processing: true,
            serverSide: true,
            responsive: true,
            stateSave: true,
            select: true,
            dom: 'Bfrtip',
            ajax: {
                url: '/measuring-points/datatable',
                type: 'post'
            },
            columns: [{
                    className: 'details-control',
                    orderable: false,
                    data: null,
                    defaultContent: '',
                    width: '10px'
                }, {
                    data: 'measuring_points.id',
                    name: 'measuring_points.id',
                    visible: false
                }, {
                    data: 'locations.name',
                    name: 'locations.name'
                }, {
                    data: 'devices.name',
                    name: 'devices.name'
                }, {
                    data: 'measuring_points.notes',
                    name: 'measuring_points.notes'
                }, {
                    data: 'measuring_points.created_at',
                    name: 'measuring_points.created_at',
                    visible: false
                }, {
                    data: 'measuring_points.updated_at',
                    name: 'measuring_points.updated_at',
                    visible: false
                }
            ],
            buttons: [{
                extend: 'create',
                editor: editor
            }, {
                extend: 'edit',
                editor: editor
            }, {
                extend: 'remove',
                editor: editor
            }, {
                extend: 'colvis',
                text: 'Colums',
                columns: ':gt(0)'
            }, {
                extend: 'collection',
                text: 'Export',
                buttons: ['csv', {
                    extend: 'print',
                    exportOptions: {
                        columns: ':visible'
                    }
                }]
            }]
        });

zajc

If I changed the order of columns where data:null column is not the first column, it works OK.

        var table = $('#measuring-points-table').DataTable({
            processing: true,
            serverSide: true,
            responsive: true,
            stateSave: true,
            select: true,
            dom: 'Bfrtip',
            ajax: {
                url: '/measuring-points/datatable',
                type: 'post'
            },
            columns: [{
                data: 'measuring_points.id',
                name: 'measuring_points.id',
                visible: false
            }, {
                className: 'details-control',
                orderable: false,
                data: null,
                defaultContent: '',
                width: '10px'
            }, {
                data: 'locations.name',
                name: 'locations.name'
            }, {
                data: 'devices.name',
                name: 'devices.name'
            }, {
                data: 'measuring_points.notes',
                name: 'measuring_points.notes'
            }, {
                data: 'measuring_points.created_at',
                name: 'measuring_points.created_at',
                visible: false
            }, {
                data: 'measuring_points.updated_at',
                name: 'measuring_points.updated_at',
                visible: false
            }],
            buttons: [{
                extend: 'create',
                editor: editor
            }, {
                extend: 'edit',
                editor: editor
            }, {
                extend: 'remove',
                editor: editor
            }, {
                extend: 'colvis',
                text: 'Colums',
                columns: ':gt(0)'
            }, {
                extend: 'collection',
                text: 'Export',
                buttons: ['csv', {
                    extend: 'print',
                    exportOptions: {
                        columns: ':visible'
                    }
                }]
            }]
        });

zajc

For workaround i've created additional hidden column which is not data:null

<table class="table table-striped table-bordered" id="measuring-points-table">
    <thead>
        <tr>
            <th></th>//<---------
            <th></th>
            <th>ID</th>
            <th>Location</th>
            <th>Device</th>
            <th>Notes</th>
            <th>Created At</th>
            <th>Updated At</th>
        </tr>
    </thead>
</table>

I've repeated data: 'measuring_points.id column.

   columns: [{
                    data: 'measuring_points.id', //<---------
                    name: 'measuring_points.id',
                    visible: false
                }, {
                    className: 'details-control',
                    orderable: false,
                    data: null,
                    defaultContent: '',
                    width: '10px'
                }, {
                    data: 'measuring_points.id',
                    name: 'measuring_points.id'
                }

colin

Odd, it should work - see here: http://live.datatables.net/kacitilu/1/edit (note the editing won't work - it's just to demonstrate the empty column). There it has a similar setup as yours, with the extra column at the start.

Could you look at that, please, and see if it helps. If it's still not working for you, please can you update my example, or link to your page, so that we can see the problem.

Cheers,

Colin

kthorngren

I think the error as to do with the ordering of the table. By default the order is set to column 0 which has your data: null. Your server side script is trying to sort by this column. Try using order to order the table by one of your data columns.

EDIT: If this works you may want to set columns.orderable false for this column.

Kevin

zajc

If I set order: [[ 1, 'asc' ]], and set stateSave: false it works. It doesn't work if it is set to stateSave: true. I don't know why stateSave affects order.

As @kthorngren wrote, now I also think the problem is default order by column 0 which is in my case null. I cannot debug what kind of SQL statement is because all I get is

{
   "fieldErrors":[

   ],
   "error":"Unknown field:  (index 0)",
   "data":[

   ],
   "ipOpts":[

   ],
   "cancelled":[

   ],
   "debug":[

   ]
}

@colin your example is not dealing with the real database (in my case MySQL) but only text dataset, so there is no SELECT statement involved.

kthorngren

The stateSave option affects the order because the order is saved. Sounds like it is loading the order saved when the order was set to column 0. To get around that I would try enabling the deferLoading option to stop the first request to the server with stateSave enabled. Now the saved state should be with the column ordering you defined with order. Remove the deferLoading option and reload the page.

If you wanted you can use one of the SSP JS BIN templates found here. You can change Colin's example to use one of the SSP URLs and enable server side processing.

Kevin

zajc

My newest workaround now is to use known column instead data:null and than render values to null.

          {
                className: 'details-control',
                orderable: false,
                data: 'measuring_points.id',
                defaultContent: '',
                width: '10px',
                render: function() {
                    return null;
                }
            }

This works fine. It is not logical, but it is working as I expected and I can continue to work.

If I will find some time I am going to create an example with SSP.

kthorngren

Actually probably the only thing you need to do is sort the table by another column and reload the page. It should save the state with the column you just sorted. You can use stateSaveParams or stateLoadParams to remove the column sorting if you want the page to always sort by what you define with order.

Kevin