Cant get an id column

Cant get an id column

minobuminobu Posts: 23Questions: 8Answers: 2

All other columns work except for the id column. Column name is correct. Its an int column from mysql database, other int cols seem to work fine just not this one. I get this JS error then the rest of the cols load however the id is empty.

DataTables warning: table id=lease_units - Requested unknown parameter 'id' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

Code is posted below.

Editor::inst( $db, 'lease_units', 'id' )
    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'site' ),
        Field::inst( 'lease' ),
        Field::inst( 'unit_type' ),
        Field::inst( 'batch_id' ),
        Field::inst( 'grade' ),
        Field::inst( 'total_units' ),
        Field::inst( 'extra1' ),
        Field::inst( 'extra2' ),
        Field::inst( 'attrition_oysters' ),
        Field::inst( 'avg' ),
        Field::inst( 'bio_mass' ),
        Field::inst( 'date_arrived' )
            ->validator( Validate::dateFormat( 'd-m-y' ) )
            ->getFormatter( Format::dateSqlToFormat( 'd-m-y' ) )
            ->setFormatter( Format::dateFormatToSql( 'd-m-y' ) ),
        Field::inst( 'fy_saleable' )

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






<thead>
                    <tr>

                        <th>Id</th>
                        <th>Site</th>
                        <th>Lease</th>
                        <th>Unit</th>
                        <th>Batch</th>
                        <th>Grade</th>
                        <th>Units</th>
                        <th>Value</th>
                        <th>Oysters</th>
                        <th>Post</th>
                        <th>Density</th>
                        <th>Bio mass</th>
                        <th>Deployment</th>
                        <th>Harvest Fy</th>
                    </tr>
                </thead>
                <tfoot>
                <tr>
                    <th style="text-align:center">Totals:</th>

                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                </tr>
                </tfoot>
            </table>






            var table = $('#lease_units').DataTable( {

                    dom: 'fBQPrtip',
                    ajax: 'table.lease_units.php',
                    columns: [
                        {
                            data: "id" //0
                        },
                        {
                            data: "site" //1
                        },
                        {
                            "data": "lease" //2
                        },
                        {
                            "data": "unit_type"//3
                        },
                        {
                            "data": "batch_id"//4
                        },
                        {
                            "data": "grade"//5
                        },
                        {
                            "data": "total_units",//6
                            "type": "num-fmt"
                        },
                        {
                            "data": "extra1",//7
                            "type": "num-fmt"
                        },
                        {
                            "data": "extra2",//8
                            "type": "num-fmt"
                        },
                        {
                            "data": "attrition_oysters",//9
                            "type": "num-fmt"
                        },
                        {
                            "data": "avg",//10
                            "type": "num-fmt"
                        },
                        {
                            "data": "bio_mass",//11
                            "type": "num-fmt"
                        },
                        {
                            "data": "date_arrived"//12
                        },
                        {
                            "data": "fy_saleable"//13
                        },
                    ],
                    select: true,
                    lengthChange: false,


                buttons: [
                    { extend: "create", editor: editor },
                    { extend: "edit",   editor: editor },
                    { extend: "remove", editor: editor },




                    {
                        extend: 'excelHtml5',
                        autoFilter: true,
                        footer: true,
                        filename: 'Deployed units_',
                        sheetName: 'Deployed units'
                    },

                ],

                searchPanes: {
                    cascadePanes: true,
                        controls: true,
                        collapse: false,
                        layout: 'columns-1'
                },





                    columnDefs:[
                        {
                            searchPanes:{
                                show: false,
                            },
                            targets: [0,6,7,8,9,10,11,12],
                        },


                        {
                            targets: [6,8,9,10],
                            render: $.fn.dataTable.render.number(',', '.', 0, '')
                        },

                        {
                            targets: [7],
                            render: $.fn.dataTable.render.number(',', '.', 2, '$')
                        },

                        {
                            targets: [11],
                            render: $.fn.dataTable.render.number(',', '.', 2, '')
                        },




                    ],


                    scrollY:"60vh",
                    scrollX:1400,
                    scroller:true,
                    select: true,
                    fixedHeader: true,


                    "language": {
                        "search": "",
                        "clearMessage": "&#10006;",
                    },


                    "order": [[0, 'desc']],

                    "footerCallback": function ( row, data, start, end, display ) {
                        var api = this.api(), data;

            // Remove the formatting to get integer data for summation
                        var intVal = function ( i ) {
                            return typeof i === 'string' ?
                                i.replace(/[\$,]/g, '')*1 :
                                typeof i === 'number' ?
                                    i : 0;
                        };


            // Total over all pages TOTAL UNITS
                        total6 = api
                            .column( 6,{ search:'applied'}  )
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );





            // Total over all pages OYSTERS PRE
                        total7 = api
                            .column( 7,{ search:'applied'}  )
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );



            // Total over all pages OYSTERS POST
                        total8 = api
                            .column( 8,{ search:'applied'}  )
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );



                        // Total over all pages
                        total9 = api
                            .column( 9,{ search:'applied'}  )
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );

                        // Total over all pages
                        total11 = api
                            .column( 11,{ search:'applied'}  )
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );







            // Update footer



                        total6 = numeral(total6).format('0,0');
                        $( api.column( 6).footer() ).html(


                            ''+ total6 +''
                        );



                        total7 = numeral(total7).format('0,0');
                        $( api.column( 7).footer() ).html(


                            ''+ total7 +''
                        );




                        total8 = numeral(total8).format('0,0');
                        $( api.column( 8).footer() ).html(


                            ''+ total8 +''
                        );

                        total9 = numeral(total9).format('0,0');
                        $( api.column( 9).footer() ).html(


                            ''+ total9 +''
                        );

                        total11 = numeral(total11).format('0,0');
                        $( api.column( 11).footer() ).html(


                            ''+ total11 +''
                        );


                    },

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,257Questions: 26Answers: 4,761

    Did you follow the troubleshooting steps at the link in the error?
    http://datatables.net/tn/4

    The error suggests that Datatables can't find the id object in the returned rows. By default it will expect to find this structure:

    {
      "data": [
        { "id": 1, "site": "xyz", .... },
        ....
      ]
    }
    

    See the Ajax docs for details. Use the browser's network inspector tool to see the JSON response. If you still need help post a snippet of the JSON response so we can see your data structure.

    Kevin

  • minobuminobu Posts: 23Questions: 8Answers: 2

    Thanks, Yes i did follow that guide, I spent quite a few hours trying to figure it out before coming here. JSON response is below. Not sure why its returning "DT_RowId":"row_17029" the 17029 is correct but not sure why its prefixing row_ to it or what DT_RowId is. column in the database is "id".

    {"data":[{"DT_RowId":"row_17029","site":"Cromarty Bay","lease":"Cromarty Bay","unit_type":"8mm Tooltech","batch_id":"2021 Forster","grade":"25mm","total_units":"5","extra2":"2000","extra1":"1200","attrition_oysters":"1860","date_arrived":"22-06-23","avg":"400","bio_mass":"0","fy_saleable":"24"},{"DT_RowId":"row_16162","site":"Wallis Lakes","lease":"Sciaccas Paddock","unit_type":"Tray - Wooden 9ft","batch_id":"2020 Forster","grade":"20mm","total_units":"61","extra2":"48800","extra1":"24400","attrition_oysters":"45384","date_arrived":"28-04-23","avg":"800","bio_mass":"0","fy_saleable":"23"}

  • minobuminobu Posts: 23Questions: 8Answers: 2
    Answer ✓

    This was my bad. I had an old version of JS file and i was still pointing to that. JSON response put me on the right track. Thanks.

Sign In or Register to comment.