How to setup a Select Filter

Mike Storey · January 2015

Pardon the new-b question, I'm still pretty new to jQuery. I'd like to setup a selectmenu, populated by a json query, and on change in that control, repopulate the table editor using the selected value as a parameter in the post call. I'm guessing it's pretty easy. A pointer to a sample would be greatly appreciated, as I'm still at the copy/paste level of competency with several of these technologies.

allan · January 2015

I think you might need to elaborate a little bit more on what you are looking for please.

You can use the field().node() method to get the container node for a field and then use a bit of jQuery to attach an event listener to trigger an action on change. For example:

$('select', editor.field('myField').node()).on( 'change', function () {
  var val = $(this).val();

  ... ajax
} );

Assuming I've understood correctly.

A new dependent method will be available in Editor 1.4 (in the next beta) which will make this much easier.

Allan

Mike Storey · January 2015

I think I'm getting closer to my desired outcomes.... but still struggling with what I suspect are very simple items. Here is a more complete description of what I'm trying to accomplish. I have a javascript variable customerID, that I want to pass to the server side PHP code, and have that code use the value in a where clause on the generated select statement. I suspect I will need something like the following....

<script> ...
    var customerID = 0;
    .....
    $('#staff').dataTable( {
        "dom": "Tfrtip",
        "ajax": "php/table.staff.php",
        "data": customerID,
        ... }

    setCustomer(newId) {
        customerID = newId;
        ${#staff).reload();
    
... </script>

And in then in table.staff.php

<?php
...
$myEditor.getQuery.addwhere("idcustomer",$_REQUEST.data,"=");
...

But alas, no "getQuery" that I can find.... I may be going about it all wrong, but my objective is to filter the data based on the contents of a javascript variable, and instigate a "refresh" after changing the value. I want the total row count to reflect the filter, so customer X my have 20 rows, and customer Y may have 15 rows. I want the table count in the footer to reflect this as well. It's a kind of "pre" filter of data before it goes into the DataTable, as opposed to fetching all the data and then "searching" it in the Data Table.

allan · January 2015

I see - so you want to reduce the data shown in the DataTable by the where condition.

The way to do that is to send the additional data to the server-side script using the ajax.data option. For example:

    $('#staff').dataTable( {
        "dom": "Tfrtip",
        "ajax": {
          url: "php/table.staff.php",
          type: "POST",
          data: function ( d ) {
            d.customerId = newId;
          }
        }
     ...

Then you can use ajax.reload() if you want to change the customer id (the newId variable in this case - you could read from a select list or whatever to get the value).

Then in your server-side script for Editor you can use the where() method to add the required condition - for example:

->where( 'customerid', $_POST['customerId'] )

That will do the "pre-filter" your want.

Allan

Mike Storey · January 2015

Thanks!

Mike Storey · January 2015

Ok, so the table is now populating as I wanted, but the bubble form is now reporting an Editor System Error on update. The data in the database IS updated, but not in the web page. Simple testing point's to the ->where() statement in table.replace.php (If I remove it the error goes away along with the filter). The Safari console shows the ajax request/response and it all looks good with a return status of 200. I can see the request payload, but either Safari doesn't show the reply content or I can't figure out where to look. I'll paste what I think is the relevant code below. I will get the application installed at OpenShift but that may be a few days so please don't wait if you have ideas on what the problem is.

<html>
    <script type="text/javascript" charset="utf-8" src="js/table.customer.js"></script>
    <script type="text/javascript" charset="utf-8" src="js/table.replace.js"></script>
....
    <script>
        var theCustomer = 0;
    </script>
</html>

table.customer.js

    $('#customer').dataTable( {
        dom: "Tfrtip",
        ajax: "php/table.customer.php",
        ...
        "tableTools": {
            "sRowSelect": "single",
            "fnRowSelected": function ( nodes ) {setCustomer(nodes);},          
        ...
    } );
    customerTable = $("#customer").DataTable();
    
    function setCustomer(nodes) {
        var theName = customerTable.row(nodes).data().shortName;
        theCustomer = customerTable.row(nodes).data().idcustomer;
        $( ".spanCustomerName" ).text( theName );
        replaceTable.ajax.reload();
    }

table.replace.js

    $('#replace').dataTable( {
        dom: "Tfrtip",
        "ajax": {
            url: "php/table.replace.php",
            type: "POST",
            data: function ( d ) {d.customerId = theCustomer;}
        ...
    } );
    replaceTable = $('#replace').DataTable();

table.replace.php

Editor::inst( $db, 'replace', 'idreplace' )
    ->fields(
        Field::inst( 'replace.idreplace' )->set( false ),
        Field::inst( 'replace.idcustomer' )->options( 'customer', 'idcustomer', 'shortName' ),
        Field::inst( 'replace.description' ),
        Field::inst( 'replace.from' ),
        Field::inst( 'replace.to' ),
        Field::inst( 'replace.hint' ),
        Field::inst( 'replace.environment' )->options( 'codesEnvironment', 'code', 'meaning' ),
        Field::inst( 'codesEnvironment.meaning' ),
        Field::inst( 'customer.shortName' )
    )
    ->where( 'replace.idcustomer', $_POST['customerId'] )
    ->leftJoin( 'codesEnvironment', 'codesEnvironment.code', '=', 'replace.environment' )
    ->leftJoin( 'customer', 'customer.idcustomer', '=', 'replace.idcustomer' )
    ->process( $_POST )
    ->json();

allan · January 2015

The error that you are seeing is presumably related to the fact that $_POST['customerId'] is not defined when Editor makes its Ajax submission?

If so, then you need to submit that data using ajax.data in exactly the same way that you've used DataTables' own ajax.data option - i.e. you need to send the additional information required.

Allan

Mike Storey · January 2015

Looking at the request/reply in the Safari debugger I can see that the customerID is in the request, however it has a value of 0, and the datatable-ajax-post-data callback function in table.replace.js is not called prior to the update being sent to the server. Since this is only on the calls from the editor bubble, I'm not sure how I can change what is submitted when "update" is clicked, or why that callback function is not called before the update. If you can provide me with a little more direction on how to override the ajax call being used by the bubble editor that would be great.

allan · January 2015

Could you show me the code you are using please? The ajax.data callback should be getting called every time Editor makes an Ajax request.

Allan

Mike Storey · January 2015

I tried to put all the relevant code above, so I'll put the full content here. The only code in the html file is "var theCustomer = 0;" and the includes for table.replace.js and table.customer.js.

Here is the code for table.customer.js (the only relevant code here is the "setCustomer" function which set's the value of theCustomer.

/*
 * Editor client script for DB table customer
 * Automatically generated by http://editor.datatables.net/generator
 */

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: "php/table.customer.php",
        table: "#customer",
        fields: [
            {
                "label": "Short Name",
                "name": "shortName",
                "type": "text"
            },
            {
                "label": "Legal Name",
                "name": "legalName",
                "type": "text"
            },
            {
                "label": "Location",
                "name": "location",
                "type": "text"
            }
        ]
    } );

    $('#customer').dataTable( {
        dom: "Tfrtip",
        ajax: "php/table.customer.php",
        columns: [
            {"data": "shortName"},
            {"data": "legalName"},
            {"data": "location"}
        ],
        "tableTools": {
            "sRowSelect": "single",
            "fnRowSelected": function ( nodes ) {setCustomer(nodes);},          
            "aButtons": [
                { "sExtends": "editor_create", "editor": editor },
                { "sExtends": "editor_edit",   "editor": editor },
                { "sExtends": "editor_remove", "editor": editor }
            ]
        }
    } );
    customerTable = $("#customer").DataTable();
    
    function setCustomer(nodes) {
        var theName = customerTable.row(nodes).data().shortName;
        theCustomer = customerTable.row(nodes).data().idcustomer;
        $( ".spanCustomerName" ).text( theName );
        replaceTable.ajax.reload();
        patternTable.ajax.reload();
        hostTable.ajax.reload();
    }

} );

}(jQuery));

Here is table.replace.js, with the data: callback function. To test if this function was being called I simply put an alert(theCustomer); call into the function. (that statement has been removed from the code below)

/*
 * Editor client script for DB table replace
 * Automatically generated by http://editor.datatables.net/generator
 */

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: "php/table.replace.php",
        table: "#replace",
        fields: [
            {
                label: "Environment",
                name: "replace.environment",
                type: "select" 
            },
            {
                label: "Description",
                name: "replace.description",
                type: "text"
            },
            {
                label: "From",
                name: "replace.from",
                type: "text"
            },
            {
                label: "To",
                name: "replace.to",
                type: "text"
            },
            {
                label: "Hint",
                name: "replace.hint",
                type: "text"
            }
        ]
    } );

    $('#replace').dataTable( {
        dom: "Tfrtip",
        "ajax": {
            url: "php/table.replace.php",
            type: "POST",
            data: function ( d ) {d.customerId = theCustomer;}
        },
        columns: [
            { data: "codesEnvironment.meaning"  },
            { data: "replace.description"       },
            { data: "replace.from"              },
            { data: "replace.to"                },
            { data: "replace.hint"              }
        ],
        tableTools: {
            sRowSelect: "os",
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor }
            ]
        },
    } );
    replaceTable = $('#replace').DataTable();

    $('#searchReplaceEnvironment').on( 'keyup', function () {
        replaceTable
            .columns( 0 )
            .search( this.value )
            .draw();
    } );
            
} );

}(jQuery));

and finally, here is table.replace.php - if I comment out the ->where line, the updates work without errors.

<?php

/*
 * Editor server script for DB table replace
 * Automatically generated by http://editor.datatables.net/generator
 */

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

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'replace', 'idreplace' )
    ->fields(
        Field::inst( 'replace.idreplace' )->set( false ),
        Field::inst( 'replace.idcustomer' )->options( 'customer', 'idcustomer', 'shortName' ),
        Field::inst( 'replace.description' ),
        Field::inst( 'replace.from' ),
        Field::inst( 'replace.to' ),
        Field::inst( 'replace.hint' ),
        Field::inst( 'replace.environment' )->options( 'codesEnvironment', 'code', 'meaning' ),
        Field::inst( 'codesEnvironment.meaning' ),
        Field::inst( 'customer.shortName' )
    )
    ->where( 'replace.idcustomer', $_POST['customerId'] )
    ->leftJoin( 'codesEnvironment', 'codesEnvironment.code', '=', 'replace.environment' )
    ->leftJoin( 'customer', 'customer.idcustomer', '=', 'replace.idcustomer' )
    ->process( $_POST )
    ->json();

allan · January 2015

ajax: "php/table.replace.php",

In your Editor initialisation. That doesn't appear to be using the ajax.data option I mentioned. The DataTables part is using it in the ajax object - I would suggest you basically want what you have in the DataTables part in the Editor part as well - unless I'm missing something?

Allan

Mike Storey · January 2015

Duoh..... Thanks, sometimes you just need a second set of eyes (and some experience doesn't hurt)

How to setup a Select Filter

How to setup a Select Filter

Answers

Howdy, Stranger!

Categories

DataTables

How to setup a Select Filter

How to setup a Select Filter

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables