SELECT filter with JSON data

SELECT filter with JSON data

nskwortsownskwortsow Posts: 120Questions: 0Answers: 0
edited November 2012 in Editor
Hi,

The functionality of
http://datatables.net/release-datatables/examples/api/multi_filter_select.html
is very nice, but doesn't work with externally loaded data, it seems.

Do you have a suggestion as to how I can make this work with Editor?

Thanks,

Nathan

Replies

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Hi Nathan,

    The key thing to realise here is that the data is being loaded by Ajax - i.e. it is async. Therefore the code that is running in the demo to built the select menus is being run before the data has been loaded from the server!

    So what you need to do is wait until he JSON data have been loaded from the server. This can be done with fnInitComplete . The modification in my example would be:

    [code]
    $(document).ready(function() {
    /* Initialise the DataTable */
    var oTable = $('#example').dataTable( {
    "oLanguage": {
    "sSearch": "Search all columns:"
    },
    fnInitComplete: function () {
    /* Add a select menu for each TH element in the table footer */
    $("tfoot th").each( function ( i ) {
    this.innerHTML = fnCreateSelect( oTable.fnGetColumnData(i) );
    $('select', this).change( function () {
    oTable.fnFilter( $(this).val(), i );
    } );
    } );
    }
    }
    } );
    } );
    [/code]

    (i.e. just shunt it into fnInitComplete which fires when the data base been fully loaded).

    Allan
  • nskwortsownskwortsow Posts: 120Questions: 0Answers: 0
    Thanks, I'm now getting data into the function, but I still get an error:

    TypeError: sValue is undefined
    [Break On This Error]
    if (bIgnoreEmpty == true && sValue.length == 0) continue;

    ###

    when setting sValue, aData[iColumn] is undefined.

    A console.log(aData) ouputs the following:

    Object { DT_RowId=

    "row_39"

    , EquipmentName=

    "Gewichten"

    , f_LanguageID=

    "1"

    , more...}

    ###

    What's going wrong here?
  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    I don't know I'm afraid. Can you link me to the page?

    Allan
  • nskwortsownskwortsow Posts: 120Questions: 0Answers: 0
    Where it goes wrong is here:

    var aData = this.fnGetData(iRow);

    aData is correctly populated, but

    var sValue = aData[iColumn];

    results in UNDEFINED.

    - JSON is fed in correctly (data is there)
    - When I hard-code in the data field, e.g. aData.EquipmentName, it works.

    I think the difference between your code sample and my implementation is the fact that I am using a JOIN.

    This means that there are 2 dimensions of a JSON object to loop through, and aData[iColumn] is only one dimension.

    Please point me in the right direction.

    ###

    My JSON looks like this:

    DT_RowId


    "row_2"


    EquipmentName


    "Ball"


    f_LanguageID


    "0"


    tblLanguages
    Object { LanguageID=

    "0"

    , LName=

    "English"

    }
  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    > var sValue = aData[iColumn];

    Ah. That's wrong as you are using objects - not arrays.

    There isn't actually a publicly exposed function to get the data from a column based on objects at the moment (there will be when I finish the 1.10 API). Until then, in the plug-in function you are using replace:

    [code]
    var aData = this.fnGetData(iRow);
    var sValue = aData[iColumn];
    [/code]

    with:

    [code]
    var sValue = oSettings.oApi._fnGetCellData( oSettings, iRow, iColumn );
    [/code]

    Allan
  • nskwortsownskwortsow Posts: 120Questions: 0Answers: 0
    Works!
  • nskwortsownskwortsow Posts: 120Questions: 0Answers: 0
    I have several tables on the same page with which I want to use this script.
    Which parameters should I pass into

    fnGetColumnData = function ( oSettings, iColumn, bUnique, bFiltered, bIgnoreEmpty )

    I suspect that I need to pass in custom oSettings for every call?
  • nskwortsownskwortsow Posts: 120Questions: 0Answers: 0
    Never mind > jquery selector error on my side.
  • nskwortsownskwortsow Posts: 120Questions: 0Answers: 0
    Sidenote: the select boxes are only updated with new values when I do a page refresh.
    Can I link the creation of the select boxes to a different event, so that they always contain the full table content, including newly added rows?
  • nskwortsownskwortsow Posts: 120Questions: 0Answers: 0
    Sidenote 2: in one of my fields, I use MRender to determine the data shown in the column

    return (val == 0) ? "Doctor" : "Patient";

    However, the SELECT filter, only shows the 1 and 0 (my database values).

    Is there a way around this?
  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    > Can I link the creation of the select boxes to a different event, so that they always contain the full table content, including newly added rows?

    I presume that the values can be changed by Editor? If so, then you could use Editor's `onEditComplete` (and `onCreateComplete` ) events to rebuilt the search filters. It would be done by just calling a similar to function to what you've already got that will read the data front he column and rebuilt the `select` control.

    [quote]
    However, the SELECT filter, only shows the 1 and 0 (my database values).
    Is there a way around this?
    [/quote]

    Yes - the easiest way is to use the display value which can be done by altering the line of code from above to be:

    [code]
    var sValue = oSettings.oApi._fnGetCellData( oSettings, iRow, iColumn, 'display' );
    [/code]

    Note the 'new' final parameter to the function. That just tells DataTables you want the display data rather than the raw data.

    Allan
  • MarMar Posts: 1Questions: 0Answers: 0
    How I can use multi-filter with json.
    Get a javascript error "To retrieve DataTable object for the table pass no arguments or see the docs for bRetrieve and bDestroy"

    Code



    var asInitVals = new Array();

    $(document).ready(function() {


    TableTools.DEFAULTS.aButtons = [ "copy", "csv", "xls","pdf", "print" ];

    $('#example').dataTable( {
    "bJQueryUI": true,
    "sPaginationType": "full_numbers",
    "sDom": 'T<"clear">lfrtip',

    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "scripts/jsonp.php",
    "fnServerData": function( sUrl, aoData, fnCallback, oSettings ) {
    oSettings.jqXHR = $.ajax( {
    "url": sUrl,
    "data": aoData,
    "success": fnCallback,
    "dataType": "jsonp",
    "cache": false
    } );
    }

    } );

    /* Initialise the DataTable */
    var oTable = $('#example').dataTable( {
    "oLanguage": {
    "sSearch": "Search all columns:"
    }
    } );

    $("tfoot input").keyup( function () {
    /* Filter on the column (the index) of this element */
    oTable.fnFilter( this.value, $("tfoot input").index(this) );
    } );



    /*
    * Support functions to provide a little bit of 'user friendlyness' to the textboxes in
    * the footer
    */
    $("tfoot input").each( function (i) {
    asInitVals[i] = this.value;
    } );

    $("tfoot input").focus( function () {
    if ( this.className == "search_init" )
    {
    this.className = "";
    this.value = "";
    }
    } );

    $("tfoot input").blur( function (i) {
    if ( this.value == "" )
    {
    this.className = "search_init";
    this.value = asInitVals[$("tfoot input").index(this)];
    }
    } );


    } );



    Thank you very much
This discussion has been closed.