Unable to get pagination on using server side datatable.

Jeremy Wheat commented on Aug 22, 2019

Hi There,

I'm facing issue in getting pagination using server-side datatable. It shows me all the records at once either I applied all the options provided in documents. Here is the sample code :

<?php // MyReport.view.php
use \koolreport\datagrid\DataTables;
$get_heading = ["BIRTH_DATE","Document Text","testing"];
?>
<div class="report-content" style="padding:5%">
	<div class='box-container'>
	<?php

	$columns = array();
        $data_field = $this->dataStore('tablename')->data();
        
        $i=0;
        foreach($data_field[0] as $key=>$value)
        {
            $columns[$key] = array(
                "label" => $get_heading[$i],

            );
            $i++;
        }

        DataTables::create(array(
            'name' => 'DataTable1',
            "dataSource"=>$this->dataStore("tablename"),
             "columns"=>$columns,
            "options" => array(
                "searching" => true,
                "paging" => true,
                "colReorder" => true,
                "order" => [],
                "ordering" => false,
                "pageLength" => 25
            ),
            // "showFooter"=>true,
            "serverSide"=>true,
            "method"=>'post', //default method = 'get'
        ));
    ?>
    
	</div>
</div>

The result I got all data at once on page.

Please let me know what I'm doing wrong? Thanks

David Winterburn commented on Aug 23, 2019

Hi Jeremy,

For DataTables' serverSide option to work, instead of using a standard datastore the dataSource property must be a dynamic function which returns a data pipe like this:

                DataTables::create(array(
                    'name' => 'salesTable',
                    'dataSource' => function($scope) {
                        return $this->src('pdoMysql')
                        ->query('select * from customer_product_dollarsales2');
                    },
                    ...

Please try this and let us know if it works for you. Thanks!

Sherwin Gaddis commented on Nov 10, 2020

What do you mean by pdoMysql?

David Winterburn commented on Nov 11, 2020

Hi Sherwin,

"pdoMysql" in this case is a PdoDataSource returned in the report's settings method:

    //MyReport.php
    public function settings()
    {
        return [
            'pdoMysql' => [
                'connectionString' => 'mysql:host=localhost;dbname=automaker',
                'username' => 'root',
                'password' => '',
                'class' => '\koolreport\datasources\PdoDataSource',
            ]
        ];
    }

    //MyReport.view.php
    DataTables::create(array(
                    'name' => 'salesTable',
                    'dataSource' => function($scope) {
                        return $this->src('pdoMysql')
                        ->query('select * from customer_product_dollarsales2');
                    },
                    ...

Alternatively, you could use MySQLDataSource, SQLSRVDataSource, PostgreSQLDataSource, or OracleDataSource.

Sherwin Gaddis commented on Nov 11, 2020

So in my case, the pdo is patient

protected function settings()
{
    global $sqlconf, $disable_utf8_flag;
    return array(
        "dataSources"=>array(
            "patient"=>array(
                "connectionString"=>"mysql:host=localhost;dbname=" .$sqlconf["dbase"],
                "username"=>$sqlconf["login"],
                "password"=>$sqlconf['pass'],
                "charset"=>"utf8"
            )
        )
    );
}

So, I changed my view code to this

use koolreport\core\Utility as Util;
use koolreport\datagrid\DataTables;
$oneYearAgo = date('Y-m-d',strtotime(date("Y-m-d", mktime()) . " - 365 day"));
DataTables::create([
        'name' => 'Patient Utilization',
            "dataSource"=>function() use ($oneYearAgo) {
                return $this->src('patient')
                ->query("
                    select concat(p.lname,' ' , p.fname) as name,
                    (select count(*) from openemr_postcalendar_events as e where e.pc_pid = p.pid and e.pc_facility = 3 and e.pc_eventDate between $oneYearAgo and CURDATE()) as patient_appt
                    from patient_data as p
                    where p.financial_review_exp > '2019-07-01' and not testing=1
                ");
            },

            "options" => [
                "searching" => true,
                "paging" => true,
                "pageLength" => 10,
            ],
            "showFooter"=>true,
            "serverSide"=>true,
    ]);

Still does not load the data. Now I have a blank screen

KoolReport's Forum

Unable to get pagination on using server side datatable. #1053

Build Your Excellent Data Report