Hello!

I'm using your Datagrid to display data. I'm using an AJAX call. This works smooth with less records.

However performance slightly degrades if size exceeds after say 40,000 rows. You've mentioned here that we can use serverSide paging

Following is my sample code:

<?php
use \koolreport\datagrid\DataTables;
?>
<script type="text/javascript" src="js/table_kool.js"></script>
<link href="css/table_kool.css" rel="stylesheet" type="text/css">

<div id ="reportContent">
    <h3 class="tblallign titleCss">Purchase Register Report</h3>
    <?php
    DataTables::create(array(
        "dataStore" => $this->dataStore('myDataStore'),
        "options" => array(
            "searching" => true,
            "colReorder" => true,
            "paging" => true,
            "fixedHeader"=>false,
            "showFooter" => false,
            "scrollCollapse" => true,
            "lengthMenu" => [25, 50, 75, 100],
            "header" => true,
            "order" => array(
                array(1, "asc")
            )
        ),
        "serverSide" => true,
        "themeBase" => "bs4",
        "columns" => array(
            //Columns
        ),
        "cssClass" => array(
            "table" => "table table-hover table-bordered tblallign table-responsive invTbl",
            "th" => "cssHeader",
            "tr" => "cssItem,kool_mismatchedRow",
            "td" => "cssDataCol",
            "tf" => "cssfooter",
            'td' => function($row, $colName) {
            },
        )
    ));
    ?>

</div>
<script type="text/javascript" >

My question is that how can I pass custom URL in server side paging ? You've not documented this point. Currently it is performing GET request on my current URL with all parameters are appended in query string resulting 414 status code.

An example will be better.

Thank you

Well if I do that, I get

Deprecated: preg_replace_callback(): Passing null to parameter #3 ($subject) of type array|string is deprecated in /koolreport/datagrid/DataTables.php on line 1114

EDIT: I found it. I had forgotten to put in a name for the table. I think, in DataTables.php, line 1120 should read

            }, $searchStr??'');

instead of

            }, $searchStr);

, and line 1273 should read

$footerMethod = strtolower(Util::get($cSetting, "footer")??'');

instead of

$footerMethod = strtolower(Util::get($cSetting, "footer"));

You're welcome. It's my fault as I had forgotten to put the name, but maybe a useful catch. I think it'll probably be in more places. Much appreciate your quick turnaround,

Actually, followup question; to David's original note that "The only thing you need for server side to work is to provide the query and data processes (if you need) in the datasource property instead of using a datastore like other reports."

I'm really just beginning with the framework, so bear with me. I've now the whole code in the view, no longer in the controller / setup:

So here's the part from the view:

    <form method="GET">
        <div class="row">
            <div class="col-md-1 offset-md-0"><button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Load</button></div>
            <div class="col-md-0 offset-md-1" style="padding-top: 8px;">Cancelled:</div>
            <div class="col-md-3 offset-md-0">
                <div class="form-group">
                <?php
                DateRangePicker::create(array(
                    "name"=>"cancelled"
                ))
                ?>
                </div>
            </div>
            <div class="col-md-0 offset-md-1" style="padding-top: 8px;">Termination:</div>
            <div class="col-md-3 offset-md-0">
                <div class="form-group">
                <?php
                DateRangePicker::create(array(
                    "name"=>"termination"
                ))
                ?>
                </div>
            </div>
        </div>
    </form>

   <?php
    DataTables::create(array(
        "name"=>"activities_table",
        "dataSource"=>function(){
            return $this->src('tc')
            ->query("select activity_id"
                    .", order_id"
                    .", cancellation_date"
                    .", termination_date"
                    ."  where 1 = 1"
                    .(($this->params["cancelled"]  !=array())?" AND cancellation_date  > :cstart AND cancellation_date  < :cend":"")
                    .(($this->params["termination"]!=array())?" AND termination_date > :tstart AND termination_date < :tend":"")
                    )
            ->params(array(
                ":cstart" =>$this->params["cancelled"][0],
                ":cend"   =>$this->params["cancelled"][1],
                ":tstart" =>$this->params["termination"][0],
                ":tend"   =>$this->params["termination"][1]
            ))
            ;
        },
        "options"=>array(
            "paging"=>true,
            "searching"=>true,
            "pageLength" => 20,
            "lengthMenu" => [ [5, 10, 20, 50, -1], [5, 10, 20, 50, "All"] ],
            "keepConditions" => true,
            "dom"=>"Blfrtip",
            "buttons"=> [
                'copyHtml5',
                'excelHtml5',
                'csvHtml5',
                [
                    "extend" => 'pdfHtml5',
                    "orientation" => 'landscape',
                    "pageSize" => 'A4'
                ],
                "print"
            ]
        ),
        "serverSide"=>true,
        "method"=>'post',
        "columns"=>array(

As you see I'm using all sorts of table functionality.

The controller looks like this:

{
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\GETBinding;

    protected function defaultParamValues()
    {
        $cancelled_from   = (new DateTime("now"))->modify('-30 day');
        $cancelled_to     = (new DateTime("now"))->modify('+0 day');
        $termination_from = (new DateTime("now"))->modify('+0 day');
        $termination_to   = (new DateTime("now"))->modify('+60 day');


        return array(
            "cancelled"=>array(
                $cancelled_from->format('Y-m-d'),
                $cancelled_to->format('Y-m-d')
            ),
            "termination"=>array(
                $termination_from->format('Y-m-d'),
                $termination_to->format('Y-m-d')
            )
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
            "cancelled"=>"cancelled",
            "termination"=>"termination"
        );
    }

    protected function setup()
    {

    }
}

Does this make sense? Would it not be better to have the query in the controller, as in all the other examples that you've in the tutorials?

Also, I noticed that there's a GETBinding. I want to have people able to bookmark their view once they've filtered somewhere. It's not yet entirely working (like the pagesize for the pagination dropdown is not maintained), but mostly. It might be interesting to have an article about the different use cases in between GET and POSTBinding.

Thanks!

Thank you Sebastian for the suggestion. I've tried it, and it works quite well - except that it enforces a reload of the page. Digging into it a bit deeper, there seems to be a browser dependent version of pushing the state without a reload,

https://stackoverflow.com/questions/824349/how-do-i-modify-the-url-without-reloading-the-page

So apparently that's why the hashmark way of doing it is chosen by something like

https://github.com/jhyland87/DataTables-Keep-Conditions

I've worked out a - probably very amateur - version that does work.

1. In the controller, I add this (see above for the full code):

{
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\GETBinding;

    protected function defaultParamValues()
    {
        $pagelength = 50;

        return array(
            "pagelength"=>$pagelength
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
            "pagelength"=>"pagelength"
        );
    }

    protected function setup() {}
}

This I do so that I can have a default pagelength parameter. Next, in the view, I do this:

<script type="text/javascript" src="https://cdn.rawgit.com/jhyland87/DataTables-Keep-Conditions/118c5e107f1f603b1b91475dc139df6f53917e38/dist/dataTables.keepConditions.min.js"></script>

This first of all includes the above-mentioned DataTables-Keep-Conditions. I've looked at the git repository, and he writes he's not actively maintaining it, so it may make sense to have that locally.

Anyway, next I do this in the view:

 <input type="hidden" name="pagelength"/><script>var actCount=0;</script>

This gives me a form control that I can write value into. It also gives me a script variable actCount that allows me to next execute a certain procedure only once... Let's continue with the table:

    <?php
    DataTables::create(array(
        "name"=>"activities_table",
        "onReady" => "function() {
                activities_table.on('length.dt', function(e, settings, len) {
                    document.querySelector('input[name=\"pagelength\"').value = len;
                    len = document.querySelector('input[name=\"pagelength\"').value;
                    // document.querySelector('form').submit(); // This would do a page refresh
                });

                activities_table.on('xhr.dt', function(e, settings, json, xhr) {
                    if(actCount == 0) {
                        actCount = 1;

                        /* If we have a page parameter, use this one */
                        var len = '".$this->params["pagelength"]."';

                        /* Check if we have a hash, then parse that */
                        var queryString  = {};
                        var query        = window.location.hash.substring( 1 );
                        var queryparts   = query.split(\"=\");
                        var length       = queryparts[1];

                        if(length === undefined) {
                            length = len;
                        } else if (length == '_') {
                            length = 10;
                        } else {
                            length = queryparts[1];
                            console.log('Splitting');
                            length = length.split(':')
                            for (let i = 0; i < length.length; i++) {
                                if (length[i].startsWith('l')) {
                                    length = length[i].substring(1);
                                    break;
                                }
                            }
                        }

                        /* Now we have the length, let's
                         * - update the length drop down,
                         * - redraw the table
                         */
                        activities_table.page.len(length).draw();
                        activities_table.draw();
                    }
                });
            }",
        "dataSource"=>function(){

Some of this code is stolen from the DataTables-Keep-Conditions plugin. Essentially, what I am doing is that if I use that plugin, that adds something like #activities_table=l20:od1 to the url. What my little script up there does is to fish out the "20" and then to set it to the drop down, and then force the table redraw.

Note that I'm using activities_table, so the name of my table, in the Javascript. You'd have to adapt this. The actCount variable is just used because for some reason, the activities_table.on('xhr.dt', function(e, settings, json, xhr) appears to be called twice.

I hope, this helps,

M

Thanks Sebastian. It would be great if we could avoid all that scripting in each view and somehow build it into the table wrapper itself.