DataTables warning: table id=log - Unknown field: (index 2). Search input not working

DataTables warning: table id=log - Unknown field: (index 2). Search input not working

Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

Hello, I added rendered column on my page in which data is coming from dbField. Search input text for all other columns are working fine but only this rendered column I added client side has issues.

Here is the code. I tried adding searchable: true but still its giving weird error.

Error - DataTables warning: table id=log - Unknown field: (index 2)

    $('#log tfoot th').each(function() {
        var title = $(this).text();
            if(title != ""){
                $(this).html('<input type="text" style="width:100%" placeholder="Search ' + title + '" />');
            }
      });

    $(document).ready(function() {

        var table = $('#log').DataTable( {
          dom: "lfrtip",
          serverSide: true,
          processing: true,
          ajax: {
            url: "../ajax/at/log.php",
            type: "POST",
            deferRender: true,
          },

          //TABLE WINDOW
          scrollY:        "65vh",
          scrollX:        true,
          scrollCollapse: true,
          paging:         true,
          fixedColumns:   {
            left: 2
          },

          columns: [
            { data: "changeDate" },
            { data: "changeTable" },

            { data: null,

                render: function(data,type,row){

                        if(row.changeTable == 'asset A') {

                            return row.dwgTag;
                        }
                        else if(row.changeTable == 'cxprocedure C') {

                            return row.proTag;
                        }
                        else if(row.changeTable == 'loc L') {

                            return row.loc;
                        }
                        else if(row.changeTable == 'requirements R') {

                            return 'Empty';
                        }
                        else if(row.changeTable == 'users_enc U') {

                            return row.username;
                        }
                        else if(row.changeTable == 'system S') {

                            return row.systemName;
                        }
                        else if(row.changeTable == 'docstatus D') {

                            return row.docStatus;
                        }
                        else if(row.changeTable == 'room R') {

                            return row.room;
                        }
                        else if(row.changeTable == 'ci_Issues C') {

                            return row.ci_issues;
                        }
                        else {

                            return null;
                        }

                }
            },
            { data: "changes"},
            { data: "user"},
            { data: "action" },
            { data: "row"},
          ],

columnDefs: [

            { searchable: true, targets: 2 },
         ],


    initComplete: function() {
          var api = this.api();

          // Apply the search
          api.columns().every(function() {
            var that = this;

            $('input', this.footer()).on('keyup change', function() {
              if (that.search() !== this.value) {
                that
                  .search(this.value)
                  .draw();
              }
            });
          });
        },
        order: [ 0, 'desc' ]
      } );

Controller file

<?php
//SESSION START
if(!isset($_SESSION)) { 
    session_start();
    if(isset($_SESSION['userID'])) {
      $userID = $_SESSION['userID'];
  } else {
      $userID = null;
  } 
}

include("../lib/DataTables.php");

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

    Editor::inst( $db, 'changelog C', 'C.id' )
    ->field(            
      Field::inst( 'C.user', 'user' ),   
      Field::inst( 'C.action', 'action' ),
      Field::inst( 'C.row', 'row' ),
      Field::inst( 'C.changeDate', 'changeDate' )
        ->getFormatter( Format::dateSqlToFormat( 'd M Y' ) ),
      Field::inst( 'C.changeTable', 'changeTable' ),
      Field::inst( 'C.changes', 'changes'),

      // For Data Field 
      Field::inst( 'dwgTag')
      ->dbField('(SELECT A.dwgTag FROM asset A WHERE A.id = C.row)')
      ->set(false),
      Field::inst( 'loc')
      ->dbField('(SELECT L.LocationName FROM loc L WHERE L.id = C.row)')
      ->set(false),
      Field::inst( 'proTag')
      ->dbField('(SELECT CC.procedureTag FROM cxprocedure CC WHERE CC.id = C.row)')
      ->set(false),
      Field::inst( 'username')
      ->dbField('(SELECT U.username FROM users_enc U WHERE U.id = C.row)')
      ->set(false),
      Field::inst( 'systemName')
      ->dbField('(SELECT S.systemName FROM system S WHERE S.id = C.row)')
      ->set(false),
      Field::inst( 'docStatus')
      ->dbField('(SELECT D.docStatus FROM docstatus D WHERE D.id = C.row)')
      ->set(false),
      Field::inst( 'ci_issues')
      ->dbField('(SELECT CI.briefDesc FROM ci_Issues CI WHERE CI.id = C.row)')
      ->set(false),
      Field::inst( 'room')
      ->dbField('(SELECT R.room FROM room R WHERE R.id = C.row)')
      ->set(false),
)
->debug(true)
->process( $_POST )
->json();

?>

Here is the Error i am getting for null column

I am not sure what I am doing wrong here. This Data column is not available in database table. Its only client side rendering.
Please suggest if I am missing something here.

Thank you

This question has accepted answers - jump to:

Answers

  • rf1234rf1234 Posts: 2,524Questions: 79Answers: 369
    edited August 2022 Answer ✓

    You have serverside turned on. (Why?)
    The search is hence performed on the server based on database values NOT based on your client side rendering.
    Your database table won't have a field called "null", right?

    Get rid of serverSide and it should work. If you want to keep serverSide: Good luck with searching for rendered values! That is extremely hard to achieve.

  • colincolin Posts: 14,618Questions: 1Answers: 2,497
    Answer ✓

    When you see that error, what's the data being sent from the server? You'll see that in the browser's network tab.

    Colin

  • rf1234rf1234 Posts: 2,524Questions: 79Answers: 369
    edited August 2022 Answer ✓

    @colin, I guess the error is clear. I had that as well: If you have a rendered column that doesn't exist on the server you get the error. In this case it is column 2 which doesn't have a name at all (or doesn't have a name that also exists on the server).

    If you replaced "null" with an existing column name the error would disappear but of course sever side would only search in the database column and not in the rendered values. Depending on the individual circumstances this may still be satisfactory but not always. E.g. if you return rendered columns from the server it is quite unlikely that serverSide search will work well.

    You could do this for example keeping serverSide:

    { // data: null,
         data: "dwgTag",
     
                    render: function(data,type,row){
     
                            if(row.changeTable == 'asset A') {
     
                                return row.dwgTag;
                            }
                            else if(row.changeTable == 'cxprocedure C') {
     
                                return row.proTag;
                            }
                            else if(row.changeTable == 'loc L') {
     
                                return row.loc;
                            }
                            else if(row.changeTable == 'requirements R') {
     
                                return 'Empty';
                            }
                            else if(row.changeTable == 'users_enc U') {
     
                                return row.username;
                            }
                            else if(row.changeTable == 'system S') {
     
                                return row.systemName;
                            }
                            else if(row.changeTable == 'docstatus D') {
     
                                return row.docStatus;
                            }
                            else if(row.changeTable == 'room R') {
     
                                return row.room;
                            }
                            else if(row.changeTable == 'ci_Issues C') {
     
                                return row.ci_issues;
                            }
                            else {
     
                                return null;
                            }
     
                    }
                },
    
  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    @rf1234 and @colin Thank you so much for response.

    @rf1234 you are right. There is no column called Data in my database table. Thats the reason of error. So I tried adding column in database table.. without adding values to that column. Now error is gone but individual input search for Data column is not working because there are no data stored in database.

    About server side.. yes I have to keep it server side on as I have more than 150,000 rows.

    So do I have to store values in Database table for this individual text input searching to work ? what are the options if I dont want to store values in Database table ? Please suggest.

    Default Search box ( marked with blue color) is working fine.
    Individual column search for Data column is not working ( marked with red color)

Sign In or Register to comment.