server-side: searchpanes with column filter integration?

sk1597

Apologies for the lack of a test case; I tried to set one up but couldn't figure out how to load searchpanes options using the example server-side script on the live site. I'm happy to try again, if someone can point me in the right direction.

I can't for the life of me get column filters to work with viewTotal/casecadePanes when using server-side processing.

I'm using the Editor php libraries to load the searchpanes options. Selecting an option in one of the panes causes the other panes to update their options and display the number of filtered rows, as expected. Works great. When I text in a column filter (or global search), the pane options still reflect the full, unfiltered data set, and the filtered count is not displayed.

I would like for it to behave as in the [example] - is this functionality available in the Editor server-side scripts, or will they need to be modified? I played around a bit with SearchPanesOptions.php. In the exec function, I didn't see the column filters being added as 'where' conditions to the $query that gets the viewTotal counts (under the comment "// Set the query to get the current counts for viewTotal"), so I tried adding this block of code from Editor.php:
(https://datatables.net/extensions/searchpanes/examples/advanced/columnFilter.html "example ")

for ( $i=0, $ien=count($http['columns']) ; $i<$ien ; $i++ ) {
            $column = $http['columns'][$i];
            $search = $column['search']['value'];

            if ( $search !== '' && $column['searchable'] == 'true' ) {
                $query->where( $this->_ssp_field( $http, $i ), '%'.$search.'%', 'like' );
            }
        }

This got me closer: now, after applying a column filter, the ajax response includes the correct filtered counts and totals for each searchpanes option. The panes themselves, however, refuse to display the filtered counts, until I select a searchpanes option. Once I do that, the count is displayed, properly taking into account the column filter, but I would like for those counts to be displayed once the column filter is applied as in the example. Binding searchpanes.rebuildPane() to the draw() doesn't help.

My questions, to summarize:
(1) is [column filter integration] natively available with server-side processing using the Editor library, or are modifications necessary?
(2) if it should work using the unmodified libraries, is there a trick for getting it to work? (I recognize that helping me debug will be a challenge absent a test case; sorry again for that).
(3) if I need to modify the scripts: (a) am I on the right track with the block of code that I copy/pasted from Editor.php? and (b) how do I get the panes to update with the count information in the ajax response without having to first select a searchpanes option?
(https://datatables.net/extensions/searchpanes/examples/advanced/columnFilter.html "column filter integration")

Whether or not I get this to work, just want to say thanks for all you do -- datatables is an unbelievably useful project.

allan

SearchPanes should integrate okay with column filtering, as they don't use the same parameters / mechanism in the PHP libraries.

Can you show me your client-side code please? I'm wondering if there is just a missing link in triggering a draw for the column filtering since you note that isn't working.

Allan

sk1597

Thanks Allan. To make sure my customizations weren't the issue, I just created a new configuration using the Editor generator and made very few modifications beyond adding my db config, adding ->searchPaneOptions( SearchPaneOptions::inst()) for certain columns to the server script, and adding column filter code identical to the basic example. Same behavior: after entering column filter text, searchpanes filtered counts do not display.

Here's my js:

/*
 * Editor client script for DB table _evictions
 * Created by http://editor.datatables.net/generator
 */

(function ($) {

    $(document).ready(function () {


        var editor = new $.fn.dataTable.Editor({
            ajax: 'php/table._evictions.php',
            table: '#_evictions',
            fields: [
                {
                    "label": "FileDate:",
                    "name": "filedate",
                    "type": "datetime",
                    "format": "ddd, D MMM YY"
                },
                {
                    "label": "LocCode:",
                    "name": "loccode"
                },
                {
                    "label": "CaseRefNum:",
                    "name": "caserefnum"
                },
                {
                    "label": "Caption:",
                    "name": "caption"
                },
                {
                    "label": "Disposition:",
                    "name": "disposition"
                }
            ]
        });

        var table = $('#_evictions').DataTable({
            "processing": true,
            "serverSide": true,
            dom: "Pfrtip",
            ajax: {
                url: "php/table._evictions.php",
                //      "url": 'php/getdata.php',
                type: "POST",
            },
            columns: [
                {
                    "data": "filedate"
                },
                {
                    "data": "loccode"
                },
                {
                    "data": "caserefnum"
                },
                {
                    "data": "caption"
                },
                {
                    "data": "disposition"
                }
            ],
            select: true,
            lengthChange: false,
            searchPanes: {
                viewTotal: true
            },
        });

        new $.fn.dataTable.Buttons(table, [
            { extend: "create", editor: editor },
            { extend: "edit", editor: editor },
            { extend: "remove", editor: editor }
        ]);

        table.buttons().container()
            .appendTo($('.col-md-6:eq(0)', table.table().container()));

        $('#_evictions thead tr').clone(true).appendTo('#_evictions thead');

        $('#_evictions thead tr:eq(1) th').each(function (i) {
            var title = $(this).text();
            $(this).html('<input type="text" placeholder="Search ' + title + '" />');

            $('input', this).on('keyup change', function () {
                if (table.column(i).search() !== this.value) {
                    table
                        .column(i)
                        .search(this.value)
                        .draw()
                        .searchPanes.rebuildPane();  //adding this doesn't seem to do much
                }
            });
        });
    });

}(jQuery));

html:

<!doctype html>
<html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
        
        <title>DataTables Editor - _evictions</title>

        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.23/b-1.6.5/b-colvis-1.6.5/b-html5-1.6.5/b-print-1.6.5/cr-1.5.3/kt-2.5.3/sp-1.2.2/sl-1.3.1/datatables.min.css">
        <link rel="stylesheet" type="text/css" href="css/generator-base.css">
        <link rel="stylesheet" type="text/css" href="css/editor.bootstrap4.min.css">

        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.11.0/umd/popper.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.23/b-1.6.5/b-colvis-1.6.5/b-html5-1.6.5/b-print-1.6.5/cr-1.5.3/kt-2.5.3/sp-1.2.2/sl-1.3.1/datatables.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/editor.bootstrap4.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/table._evictions.js"></script>
    </head>
    <body class="bootstrap4">
        <div class="container">

            <h1>
                DataTables Editor <span>_evictions</span>
            </h1>
            
            <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="_evictions" width="100%">
                <thead>
                    <tr>
                        <th>FileDate</th>
                        <th>LocCode</th>
                        <th>CaseRefNum</th>
                        <th>Caption</th>
                        <th>Disposition</th>
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th>FileDate</th>
                        <th>LocCode</th>
                        <th>CaseRefNum</th>
                        <th>Caption</th>
                        <th>Disposition</th>
                    </tr>
                </tfoot>
            </table>

        </div>
    </body>
</html>

and php:

<?php

/*
 * Editor server script for DB table _evictions
 * Created by http://editor.datatables.net/generator
 */

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

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\SearchPaneOptions,
    DataTables\Editor\ValidateOptions;

// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.
$db->sql( "CREATE TABLE IF NOT EXISTS `_evictions` (
    `CaseRefNum` int(10) NOT NULL auto_increment,
    `filedate` date,
    `loccode` varchar(255),
    `caserefnum` numeric(9,2),
    `caption` varchar(255),
    `disposition` varchar(255),
    PRIMARY KEY( `CaseRefNum` )
);" );

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, '_evictions', 'CaseRefNum' )
    ->fields(
        Field::inst( 'filedate' )
            ->validator( Validate::dateFormat( 'D, j M y' ) )
            ->getFormatter( Format::dateSqlToFormat( 'D, j M y' ) )
            ->setFormatter( Format::dateFormatToSql( 'D, j M y' ) ),
        Field::inst( 'loccode' )
            ->searchPaneOptions( SearchPaneOptions::inst()),
        Field::inst( 'caserefnum' ),
        Field::inst( 'caption' ),
        Field::inst( 'disposition' )
            ->searchPaneOptions( SearchPaneOptions::inst())
    )
    ->process( $_POST )
    ->json();

Really appreciate your looking into this.

allan

Oh sorry - I might have misunderstood a bit. The column filter is actually working, but SearchPanes' counters aren't displaying?

Allan

sk1597

Sorry for being unclear. Yes, column filters are working properly on their own. The searchpanes always display the total counts, and after selecting a searchpanes option, the filtered counters display in the other panes as expected. But the counters are not interacting with the column filters at all: if no searchpanes option has been selected, the filtered counts do not display after applying a column filter; if a searchpanes option has been selected thereby activating the filtered counters, the counters do not update to reflect column filters. In short, viewTotal only seems to be working when the table is filtered via searchpanes.

In the Column Filter Integration example (https://datatables.net/extensions/searchpanes/examples/advanced/columnFilter.html), if I type "software" into the position column filter, the searchpanes count the visible rows and display the totals. My column filters work fine on their own, but do not seem to interact with the searchpanes in any way.

Thanks so much ... I've been spinning my wheels on this issue for days.

colin

Ah, please note the comment on searchPanes.viewTotal :

Note: When loading SearchPanes options over ajax, but then using serverSide processing, neither searchPanes.viewTotal or searchPanes.cascadePanes are supported. This is due to the requirements for users to provide accurate data for the SearchPanes options, whose values change when using these two options. While it is still possible to enable these options, it is not advised and may lead to unexpected behaviour.

So it would be best to disable that feature, I'm afraid.

Colin

sk1597

Thank you, Colin! I saw that note in a few places and apparently misinterpreted it. Much appreciation.

stevevance

I had the same issue as @sk1597 and I spun my wheels for only an hour before coming across the documentation that @colin pointed to.

I would like to request that Server Side Processing update the SearchPanesOptions and counts based on filtering.

My website is a real estate information platform, and people want to filter properties, but it's hard to know which property type to choose from (there are dozens that the tax assessor uses) if they don't know which property types are available in a person's selected geography.

sandy

Hi @stevevance ,

SearchPanes should support both searchPanes.cascadePanes and searchPanes.viewTotal when using a fully serverSide processing setup, so this may still be possible but you will have to do it that way rather than loading options purely over ajax.

SearchPanes has undergone a complete rewrite since this post was last updated. The code for this is currently in the nightly builds. I've noticed a few issues there this morning though which I have raised in an issue internally (DD-2476 for my reference). I'll report back here when I've got those fixed.

Thanks,
Sandy

sandy

Hi @stevevance ,

I've made a daft mistake - was looking at the wrong branch when testing SearchPanes out for SSP. When SearchPanes 2 is released, these will also be released and you should be able to use searchPanes.cascadePanes along with serverSide processing just fine. We hope the release will be within the next few weeks.

Thanks,
Sandy

allan

I'll tag up and release SearchPanes 2 tomorrow - keep an eye out for it

Allan

stevevance

Hi Sandy and allan, I see the new SearchPanes 2 release. I installed it and it cascades correctly, but it cascades on the full table.

My Editor SSP has several $editor->where statements to limit results from the full table based on overlapping geography.

Can these same WHERE statements be automatically applied to each field's searchPaneOptions method?

That way I don't have to add the WHERE statements to each of the fields because they could grab it from the $editor.

colin

I'm not sure if that's possible, but I'll leave it for Sandy to reply - he'll be able to reply towards the end of the week,

Colin

sandy

Hi @stevevance ,

We have some concerns over how implementing this may affect the behaviour of other aspects of error, joins being one of them. So it's not a change we will make to the editor libraries at this time.

Instead you could write a function and pass that into where statements on all of the SearchPaneOptions. That would mean that changes would only have to be made in once place. Something like the below...

$editor->where( function ( $q ) {
    $q->where( 'age', 18, '>' );
    $q->or_where('office', 'Edinburgh');
} );

You can get more details on how to pass functions into where calls on this page.

Thanks,
Sandy

stevevance

@sandy What you suggested is exactly what I ended up figuring out myself a few hours before you mentioned it!

The issue is that I have to apply that filter to each and every SearchPaneOptions method, and there're about five for now. Although I should probably write a function to reduce the code.