KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Server side pagination on large data set #796

Open BDO India LLP opened this topic on on Apr 11, 2019 - 9 comments

BDO India LLP commented on Apr 11, 2019

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

David Winterburn commented on Apr 12, 2019

Hi,

You don't need a custom get url for our DataTables' server side processing because we use the current report url. 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.

    DataTables::create(array(
        'name' => 'DataTable1',
        'dataSource' => function() {
            return $this->src('employees')
            ->query('select * from employees_salaries');
        },
        "serverSide"=>true,
        "method"=>'post', //default method = 'get'
    ));

Our DataTables would automatically analyse searching, paging and ordering request parameters to build query for the server. You don't have to handle them yourself or build a manual get/post data url like with the opensource DataTables.

Please try this and let us know if you have any difficulty. Thanks!

eMaX commented on Nov 15

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"));
Sebastian Morales commented on Nov 16

Tks, it's a great solution to avoid this warning with PHP 8.1. We will fix this in the next version of Datagrid. Much appreciated,

eMaX commented on Nov 16

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,

eMaX commented on Nov 16

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!

Sebastian Morales commented on Nov 16

You have had many insights. The reason for server-side paging DataTables to set the query in view, i.e widget create code, is we need DataTables to know its data source to modify the data source later for server-side paging/searching/sorting. If we set the query in report setup DataTables can not know its original data source without additional code. That is why we choose for a simpler solution by setting data source directly in view. Nevertheless, we will think of another solution to set query in report setup for DataTables' server-side to work.

I see that you want to use GET for users to be able to save/bookmark a report together with its input values via its url. If you want to save DataTables' page size, page number, sorting, etc as well there's a solution to use additional hidden inputs which sync with DataTables' state via its event handlers. Here's a pseudo code of the idea:

//MyReport.view.php
<form method="get">
    <input type="hidden" name="dt_page_size />
    ...
    <?php
        DataTables::create(array(
            "name" => "mydt",
            ...
            "onReady" => "function() {
                mydt.on('length.dt', function(e, settings, len) { //DataTables' page length change event
                    document.querySelector('input[name=\"dt_page_size\"').value = len;
                    document.querySelector('form').submit(); // submit the form to save hidden input value to url
                });
                ...
            }",
        ));
    ?>
</form>
eMaX commented on Nov 17

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

Sebastian Morales commented on Nov 21

It's an excellent solution for saving DataTables' state. I think many users would find your method quite helpful. Best regards,

eMaX commented on Nov 21

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.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
wiki
help needed

None