This is a laravel 4 package for the server and client side of datatables at http://datatables.net/

Nils Plaschke

Last update: Dec 30, 2022

Related tags

Laravel Datatable

Overview

Datatable

Important

This package will not receive any new updates! You can still use this package, but be preparared that there is no active development for this project.

This package is abandoned and not recommended for new projects. We recommend to use instead Yajra's Package which offers a nearly-similar API.

Introduction

This is a Laravel 4 package for the server and client side of datatables at http://datatables.net/

A Laravel 5 package is close to being completed. To install it:
    composer require chumper/datatable "dev-develop"
If you find any issues, please report them in the bug tracker!

Please Note, if you want Datatable 1.10 support & Laravel 5 support, try out our newest branch!

If you upgrade from version 2.1.* or below please make sure you adjust your app.php with the new alias:

    // aliases array:

    //old
    //'Datatable' => 'Chumper\Datatable\Facades\Datatable',

    //new
    'Datatable' => 'Chumper\Datatable\Facades\DatatableFacade',

Known Issues

none i know of so far

TODO

fix incoming bugs
code documentaion

Features

This package supports:

Support for Collections and Query Builder
Easy to add and order columns
Includes a simple helper for the HTML side
Use your own functions and presenters in your columns
Search in your custom defined columns ( Collection only!!! )
Define your specific fields for searching and ordering
Add custom javascript values for the table
Tested! (Ok, maybe not fully, but I did my best :) )

Please note!

There are some differences between the collection part and the query part of this package. The differences are:

Difference	Collection	Query
Speed	-	+
Custom fields	+	+
Search in custom fields	+	-
Order by custom fields	+	-
Search outside the shown data (e.g.) database	-	+

For a detailed explanation please see the video below. http://www.youtube.com/watch?v=c9fao_5Jo3Y

Please let me know any issues or features you want to have in the issues section. I would be really thankful if you can provide a test that points to the issue.

Installation

This package is available on http://packagist.org, just add it to your composer.json

"chumper/datatable": "2.*"

Alternatively, you can install it using the composer command:

    composer require chumper/datatable "2.*"

It also has a ServiceProvider for usage in Laravel4. Add these lines to app.php:

    // providers array:
	'Chumper\Datatable\DatatableServiceProvider',

    // aliases array:
    'Datatable' => 'Chumper\Datatable\Facades\DatatableFacade',

You can then access it under the Datatable alias.

To override the default configuration options you can publish the config file.

php artisan config:publish chumper/datatable

You may now edit these options at app/config/packages/chumper/datatable/config.php.

Basic Usage

There are two ways you can use the plugin, within one route or within two routes:

Two routes

Create two routes: One to deliver the view to the user, the other for datatable data, eg:

    Route::resource('users', 'UsersController');
    Route::get('api/users', array('as'=>'api.users', 'uses'=>'UsersController@getDatatable'));

Your main route will deliver a view to the user. This view should include references to your local copy of datatables. In the example below, files were copied from the datatables/media directories and written to public/assets. Please note that the scripts must be located above the call to Datatable:

    <link rel="stylesheet" type="text/css" href="/assets/css/jquery.dataTables.css">
    <script type="text/javascript" src="/assets/js/jquery.js"></script>
    <script type="text/javascript" src="/assets/js/jquery.dataTables.min.js"></script>

    {{ Datatable::table()
    ->addColumn('id','Name')       // these are the column headings to be shown
    ->setUrl(route('api.users'))   // this is the route where data will be retrieved
    ->render() }}

Create a controller function to return your data in a way that can be read by Datatables:

    public function getDatatable()
    {
        return Datatable::collection(User::all(array('id','name')))
        ->showColumns('id', 'name')
        ->searchColumns('name')
        ->orderColumns('id','name')
        ->make();
    }

You should now have a working datatable on your page.

One route

In your route you should use the Datatable::shouldHandle method which will check whether the plugin should handle the request or not.

    if(Datatable::shouldHandle())
    {
        return Datatable::collection(User::all(array('id','name')))
            ->showColumns('id', 'name')
            ->searchColumns('name')
            ->orderColumns('id','name')
            ->make();
    }

The plugin will then query the same url for information. The shouldHandle method just checks for an ajax request and if sEcho is set.

HTML Example

	Datatable::table()
    ->addColumn('id',Lang::get('user.lastname'))
	->setUrl(URL::to('auth/users/table'))
    ->render();

With seperate table and script:

		$table = Datatable::table()
        ->addColumn('Email2','Email', "Test")
        ->noScript();

        // to render the table:
        $table->render()

        // later in the view you can render the javascript:
        $table->script();

This will generate a HTML table with two columns (id, lastname -> your translation) and will set the URL for the ajax request.

Note: This package will NOT include the datatable.js, that is your work to do. The reason is that for example i use Basset and everybody wants to do it their way...

If you want to provide your own template for the table just provide the path to the view in laravel style.

	Datatable::table()
        ->addColumn('id',Lang::get('user.lastname'))
    	->setUrl(URL::to('auth/users/table'))
        ->render('views.templates.datatable');

Server Example

	Datatable::collection(User::all())
    ->showColumns('id')
    ->addColumn('name',function($model)
        {
            return $model->getPresenter()->yourProperty;
        }
    )->make();

This will generate a server side datatable handler from the collection User::all(). It will add the id column to the result and also a custom column called name. Please note that we need to pass a function as a second parameter, it will always be called with the object the collection holds. In this case it would be the User model.

You could now also access all relationship, so it would be easy for a book model to show the author relationship.

	Datatable::collection(User::all())
    ->showColumns('id')
    ->addColumn('name',function($model)
        {
            return $model->author->name;
        }
    )->make();

Note: If you pass a collection of arrays to the collection method you will have an array in the function, not a model.

The order of the columns is always defined by the user and will be the same order the user adds the columns to the Datatable.

Query or Collection?

There is a difference between query() and collection(). A collection will be compiled before any operation - like search or order - will be performed so that it can also include your custom fields. This said the collection method is not as performing as the query method where the search and order will be tackled before we query the database.

So if you have a lot of Entries (100k+) a collection will not perform well because we need to compile the whole amount of entries to provide accurate sets. A query on the other side is not able to perform a search or orderBy correctly on your custom field functions.

TLTR: If you have no custom fields, then use query() it will be much faster If you have custom fields but don't want to provide search and/or order on the fields use query(). Collection is the choice if you have data from somewhere else, just wrap it into a collection and you are good to go. If you have custom fields and want to provide search and/or order on these, you need to use a collection.

Please also note that there is a significant difference betweeen the search and order functionality if you use query compared to collections. Please see the following video for more details.

http://www.youtube.com/watch?v=c9fao_5Jo3Y

Available functions

This package is separated into two smaller parts:

Datatable::table()
Datatable::collection()
Datatable::query()

The second and third one is for the server side, the first one is a helper to generate the needed table and javascript calls.

Collection & Query

collection($collection)

Will set the internal engine to the collection. For further performance improvement you can limit the number of columns and rows, i.e.:

$users = User::activeOnly()->get('id','name');
Datatable::collection($users)->...

query($query)

This will set the internal engine to a Eloquent query... E.g.:

$users = DB::table('users');
Datatable::query($users)->...

The query engine is much faster than the collection engine but also lacks some functionality, for further information look at the table above.

showColumns(...$columns)

This will add the named columns to the result.

Note: You need to pass the name in the format you would access it on the model or array. example: in the db: last_name, on the model lastname -> showColumns('lastname')

You can provide as many names as you like

searchColumns(..$fields)

Will enable the table to allow search only in the given columns. Please note that a collection behaves different to a builder object.

Note: If you want to search on number columns with the query engine, then you can also pass a search column like this

   //mysql
   ->searchColumns(array('id:char:255', 'first_name', 'last_name', 'address', 'email', 'age:char:255'))

   //postgree
   ->searchColumns(array('id:text', 'first_name', 'last_name', 'address', 'email', 'age:text'))

This will cast the columns int the given types when searching on this columns

orderColumns(..$fields)

Will enable the table to allow ordering only in the given columns. Please note that a collection behaves different to a builder object.

addColumn($name, $function)

Will add a custom field to the result set, in the function you will get the whole model or array for that row E.g.:

	Datatable::collection(User::all())
    ->addColumn('name',function($model)
        {
            return $model->author->name;
        }
    )->make();

You can also just add a predefined Column, like a DateColumn, a FunctionColumn, or a TextColumn E.g.:

	$column = new \Chumper\Datatable\Columns\TextColumn('foo', 'bar'); // Will always return the text bar
	//$column = new \Chumper\Datatable\Columns\FunctionColumn('foo', function($model){return $model->bar}); // Will return the bar column
	//$column = new \Chumper\Datatable\Columns\DateColumn('foo', DateColumn::TIME); // Will return the foo date object as toTimeString() representation
	//$column = new \Chumper\Datatable\Columns\DateColumn('foo', DateColumn::CUSTOM, 'd.M.Y H:m:i'); // Will return the foo date object as custom representation

	Datatable::collection(User::all())
    ->addColumn($column)
    ->make();

You can also overwrite the results returned by the QueryMethod by using addColumn in combination with showColumns. You must name the column exactly like the database column that you're displaying using showColumns in order for this to work.

	$column = new \Chumper\Datatable\Columns\FunctionColumn('foo', function ($row) { return strtolower($row->foo); }
	Datatable::query(DB::table('table')->select(array('foo')))
	         ->showColumns('foo')
	         ->addColumn($column)
	         ->orderColumns('foo')
	         ->searchColumns('foo')
	         ->make()

This will allow you to have sortable and searchable columns using the QueryEngine while also allowing you to modify the return value of that database column entry.

Eg: linking an user_id column to it's page listing

	$column = new \Chumper\Datatable\Columns\FunctionColumn('user_id', function ($row) { return link_to('users/'.$row->user_id, $row->username) }
	Datatable::query(DB::table('table')->select(array('user_id', 'username')))
	         ->showColumns('user_id')
	         ->addColumn($column)
	         ->orderColumns('user_id')
	         ->searchColumns('user_id')

Please look into the specific Columns for further information.

setAliasMapping()

Will advise the Datatable to return the data mapped with the column name. So instead of

	{
		"aaData":[
			[3,"name","2014-02-02 23:28:14"]
		],
		"sEcho":9,
		"iTotalRecords":2,
		"iTotalDisplayRecords":1
	}

you will get something like this as response

{
	"aaData":[
		{"id":2,"name":"Datatable","created_at":"Sun, Feb 2, 2014 7:17 PM"}
	],
	"sEcho":2,
	"iTotalRecords":2,
	"iTotalDisplayRecords":1
}

make()

This will handle the input data of the request and provides the result set.

Without this command no response will be returned.

clearColumns()

This will reset all columns, mainly used for testing and debugging, not really useful for you.

If you don't provide any column with showColumn or addColumn then no column will be shown. The columns in the query or collection do not have any influence which column will be shown.

getOrder()

This will return an array with the columns that will be shown, mainly used for testing and debugging, not really useful for you.

getColumn($name)

Will get a column by its name, mainly used for testing and debugging, not really useful for you.

Specific QueryEngine methods

setSearchWithAlias()

If you want to use an alias column on the query engine and you don't get the correct results back while searching then you should try this flag. E.g.:

		Datatable::from(DB::table("users")->select(array('firstname', "users.email as email2"))->join('partners','users.partner_id','=','partners.id'))
        ->showColumns('firstname','email2')
        ->setSearchWithAlias()
        ->searchColumns("email2")

In SQL it is not allowed to have an alias in the where part (used for searching) and therefore the results will not counted correctly.

With this flag you enable aliases in the search part (email2 in searchColumns).

Please be aware that this flag will slow down your application, since we are getting the results back twice to count them manually.

setDistinctCountGroup($value = true)

If you are using GROUP BY's inside the query that you are passing into the Datatable, then you may receive incorrect totals from your SQL engine. Setting setDistinctCountGroup (which most likely only works on MySQL) will ensure that the totals are based on your GROUP BY.

setSearchOperator($value = "LIKE")

With this method you can set the operator on searches like "ILIKE" on PostgreSQL for case insensitivity.

setExactWordSearch

Will advice the engines only to search for the exact given search string.

Specific CollectionEngine methods

setSearchStrip() & setOrderStrip()

If you use the search functionality then you can advice all columns to strip any HTML and PHP tags before searching this column.

This can be useful if you return a link to the model detail but still want to provide search ability in this column.

setCaseSensitive($boolean = 'false')

Set the search method to case sensitive or not, default is false

Table

noScript()

With setting this property the Table will not render the javascript part.

You can render it manually with

script($view = null)

Will render the javascript if no view is given or the default one and will pass the class name, the options and the callbacks.

Example:

		$table = Datatable::table()
        ->addColumn('Email2','Email', "Test")
        ->noScript();

        // to render the table:
        $table->render()

        // later in the view you can render the javascript:
        $table->script();

setUrl($url)

Will set the URL and options for fetching the content via ajax.

setOptions($name, $value) OR setOptions($array)

Will set a single option or an array of options for the jquery call.

You can pass as paramater something like this ('MyOption', 'ValueMyOption') or an Array with parameters, but some values in DataTable is a JSON so how can i pass a JSON in values? Use another array, like that: setOptions(array("MyOption"=> array('MyAnotherOption'=> 'MyAnotherValue', 'MyAnotherOption2'=> 'MyAnotherValue2')));

//GENERATE

jQuery(.Myclass).DataTable({
    MyOption: {
        MyAnotherOption: MyAnotherValue,
        MyAnotherOption2: MyAnotherValue2,
    }
});

As a sugestion, take a look at this 2 files javascript.blade.php && template.blade.php in vendor/Chumper/datatable/src/views. You'll understand all the logic and see why it's important to pass the parameter like an array (json_encode and others stuffs).

setCallbacks($name, $value) OR setCallbacks($array)

Will set a single callback function or an array of callbacks for the jquery call. DataTables callback functions are described at https://datatables.net/usage/callbacks. For example,

    ->setCallbacks(
        'fnServerParams', 'function ( aoData ) {
            aoData.push( { "name": "more_data", "value": "my_value" } );
        }'
    )

addColumn($name)

Will add a column to the table, where the name will be rendered on the table head. So you can provide the string that should be shown.

if you want to use the alias mapping feature of the server side table then you need to add an array like this

Datatable::table()
    ->addColumn(array(
        'id'            => 'ID',
        'name'          => 'Name',
        'created_at'    => 'Erstellt'
        ))
	->render();

Please note that passing an assosiative array to the addColumn function will automatically enable the alias function on the table

setAliasMapping(boolean)

Here you can explicitly set if the table should be aliased or not.

countColumns()

This will return the number of columns that will be rendered later. Mainly for testing and debugging.

getData()

Will return the data that will be rendered into the table as an array.

getOptions()

Get all options as an array.

render($view = template.blade)

Renders the table. You can customize this by passing a view name. Please see the template inside the package for further information of how the data is passed to the view.

setData($data)

Expects an array of arrays and will render this data when the table is shown.

setCustomValues($name, $value) OR setCustomValues($array)

Will set a single custom value, or an array of custom values, which will be passed to the view. You can access these values in your custom view file. For example, if you wanted to click anywhere on a row to go to a record (where the record id is in the first column of the table):

In the calling view:

{{ DataTable::table()
    ->addColumn($columns)
    ->setUrl($ajaxRouteToTableData)
    ->setCustomValues('table-url', $pathToTableDataLinks)
    ->render('my.datatable.template') }}

In the datatable view (eg, 'my.datatable.template'):

    @if (isset($values['table-url']))
        $('.{{$class}}').hover(function() {
            $(this).css('cursor', 'pointer');
        });
        $('.{{$class}}').on('click', 'tbody tr', function(e) {
            $id = e.currentTarget.children[0].innerHTML;
            $url = e.currentTarget.baseURI;
            document.location.href = "{{ $values['table-url'] }}/" + $id;
        });
    @endif

setOrder(array $order)

Defines the order that a datatable will be ordered by on first page load.

{{ DataTable::table()
    ->addColumn('ID', 'First Name', 'Last Name')
    ->setUrl($ajaxRouteToTableData)
    ->setOrder(array(2=>'asc', 1=>'asc')) // sort by last name then first name
    ->render('my.datatable.template') }}

Extras

Some extras features, using the Datatables api.

TableTools

To use TableTools you will need to add some files in your project (https://datatables.net/extensions/tabletools/), if you want some help download the datatable's package and inside the extension folder go to /tabletools and study the examples. After, all the files include, don't forget to pass the parameters like this:

//In view:

{{
    Datatable::table()
        ->addColumn('your columns here separated by comma')
        ->setUrl('your URL for server side')
        ->setOptions(
            array(
                'dom' =>"T<'clear'>lfrtip",
                'tableTools' => array(
                    "sSwfPath" => "your/path/to/swf/copy_csv_cls_pdf.swf",
                    "aButtons" => array("copy", "pdf", "xls")
                )
            )
        )
}}

If you want to get some properties like "which row did i click?", see the javascript.blade.php and the variable $values.

Contributors

jgoux for helping with searching on number columns in the database
jijoel for helping with callback options and documentation

Changelog

2.0.0:
- Seperated Query and Collection Engine
- Added single column search
- Code cleanup

Applications

https://github.com/hillelcoren/invoice-ninja (by Hillel Coren)

License

This package is licensed under the MIT License

Comments

Secho showing null - json is displaying, but not datatable

Sir, Like Rixhars ajazi posts earlier I am getting an draw (datatables v1.10 - modified for the chumper plugin as in your other responses) value of null. The json is displaying with a draw( or secho for previous versions) being returned as zero. I am using wampserver 2.4.9 with mysql.

Any ideas what is causing this? - any help greatly appreciated.

Thank you.

opened by stumped101 32
Make the grid editable

In the datatables.net website there is a example (http://datatables.net/release-datatables/examples/api/editable.html) with a editable grid using another plugin called jEditable. Will be a big plus if this package could implement this join out of the box.
enhancement

opened by gvsrepins 29
Trying to get property of non-object
Hi, thanks for nice module. I have some issue with custom column in my project. When I try to use relationship with model it produces "Trying to get property of non-object" error. Similar call in my views works like a charm and I get title of a producent. Do you have any suggestions where could be a problem?

Thanks Tom

public function table() { return Datatable::collection($this->product->all()) ->addColumn('producent', function($model) { return $model->producent->title; }) ->make(); }
opened by lachyn 19
Table headers showing but no data
Thanks for this great plugin, i need help getting to work. Table headers showing, no error and no data shown

here is my view code
{{ Datatable::table() ->addColumn('id','task', 'deadline','status') // these are the column headings to be shown ->setUrl(URL::to('tasks')) // this is the route where data will be retrieved ->render() }}

here is my controller code

public function getTable() {

return Datatable::collection(Todo::all(array('id','task','deadline','status'))) ->showColumns('id', 'task', 'deadline', 'status') ->make(); }

Here is my route in route.php Route::get('tasks', array('as'=>'tasks', 'uses'=>'TodosController@getTable'));

Your help would be massively appreciated.
opened by tolunerve 15
Data being received but table does not populate

Hello sir,

I love the simplicity of your package. It saves me a lot of time as a developer. However I am at the end of my wits here and I need help from the creator.

I am : -Using Query engine -Using server side processing (->setURL(route('allUsersJson')) and "bServerSide": true,) -When not using server side processing my data comes just fine no issues

My controller :

public function allUsersJson() { $user = DB::table('user'); return Datatable::query($user) ->showColumns('userId', 'name', 'email', 'active', 'yearFrom', 'yearTo') ->searchColumns('name', 'email') ->make(); }

my view : {{ Datatable::table()
->setURL(route('allUsersJson')) ->addColumn('Actions','Name', 'Email', 'Status', 'From', 'To') ->setOptions('bProcessing', true) ->setOptions('bSort', false) ->setOptions('iDisplayLength', '5') ->setOptions('aLengthMenu', [5,10,15,20]) ->setOptions('bAutoWidth', false) ->setOptions('sAjaxDataProp', 'aaData') ->render() }}

and in my chrome dev tools I send :

Request URL:https://www.learnlaravel.com/api/users?sEcho=1&iColumns=6&sColumns=&iDisplayStart=0&iDisplayLength=5&mDataProp_0=0&mDataProp_1=1&mDataProp_2=2&mDataProp_3=3&mDataProp_4=4&mDataProp_5=5&sSearch=&bRegex=false...............(and so on - its a very long URL)

and I receive:

{"aaData":[["1","*_","_[email protected]","1","08/2012","08/2014"],["5","RixA","_*@gmail.com","1","02/2012","03/2015"]],"sEcho":0,"iTotalRecords":"2","iTotalDisplayRecords":"2"}

I don't understand what I am missing here. Any help would be great. I also asked on the laravel 4 forums as well. Thanks in advanced.

opened by RixhersAjazi 14
Serverside ordering Columns

Using ServerSide, only the first column of the DataTable can't be ordered. The remaining columns order just fine. If "bServerSide": false, all columns order as expected.

I took a look on debug but can't solve this issue. Regards

opened by Corvisier 13
order columns

The alphabetically order not works, I have a column name and when I order by this column the order is not alphabetically.

what can be the problem?

the order is only by ID

opened by carlituxman 11
Infinite loop with doInternalSearch after recent updates

I'm having some issues using dataTables search after fetching the latest updates with composer. When I do an ajax search I get the following error:

file: "/var/www/html/myproj/vendor/chumper/datatable/src/Chumper/Datatable/Engines/QueryEngine.php" line: 274 message: "Maximum function nesting level of '200' reached, aborting!" type: "Symfony\Component\Debug\Exception\FatalErrorException"

I've already tried increasing the maximum function nesting level, but it keeps hitting it.

The issue seems to be that the same functions gets called recursively here: https://github.com/Chumper/Datatable/blob/master/src/Chumper/Datatable/Engines/QueryEngine.php#L274

I'm using the Datatable::query($query) method
bug

opened by jonasva 11
Fixing some bugs and adding new features

If I use calculated field in query I have an error because $this->originalBuilder->count(); produce "select count(*) as aggregate", so it haven't calculated fields. I haven't opportunity to test it with "distinctCountGroup" option.

opened by dlnsk 11
Chumper with eloquent relationship

Chumper with eloquent relationship. Is that possible?

model:

// it works fine public function operadora() { return $this->belongsTo('Operadora', 'id_linha', 'id_operadora'); }

// echo $linha->operadora->nome; works fine

// but this not, why?
->addColumn('operadora', function($model) { return $model->operadora->nome; })

Error: Trying to get property of non-object

opened by LeonardoCaitano 11

[Laravel 5] Merge 'setOption' with 'setCallback' to call function script any where

Merge callbacks into options. Allow add function script by 'setOptions' any where. function in TableTool: fnClick,....

You can add button or html into Datable. Example I want add tag a in Column 2 by option 'aoColumns' More.

 'aoColumns' => array(
                array(

                    // 'sTitle' => trans('management-system::units.titles.short') ,
                    'mData' => 'code',
                ) ,
                array(

                    // 'sTitle' => trans('management-system::generals.titles.option') ,
                    'mData' => "function (o) {
                        return '<a href=/Produto/Detalhar/>' + 'More' + '</a>';
                    }",
                    'sClass' => 'text-center no-padding valign-middle',
                    'sWidth' => '60pt',
                    'bSearchable' => false,
                    'bSortable' => false
                ),

Example:

Datatable::table()->setOptions(array(
            'orderMulti' => true,
            'sDom' => '<"row btn-toolbar"<"col-xs-6 btn-toolbar"lT><"col-xs-6"f>>tr<"row"<"col-xs-6"i><"col-xs-6"p>>',
            'oTableTools' => array(
                'sSwfPath' => asset('packages/thesis-gate/layout-master/assets/swf/copy_csv_xls.swf') ,
                'sRowSelect' => 'multi',
                'sRowSelector' => 'td:not(:last-child)',
                'aButtons' => array(
                    array(
                        'sExtends' => "select_all",
                        'sButtonText' => '<i class="fa fa-check-square-o"></i>',
                        'sButtonClass' => 'btn-sm',
                        'sToolTip' => trans("management-system::generals.buttons.select") ,
                    ) ,
                    array(
                        'sExtends' => 'select_none',
                        'sButtonText' => '<i class="fa fa-square-o"></i>',
                        'sButtonClass' => 'btn-sm',
                        'sToolTip' => trans("management-system::generals.buttons.deselect") ,
                    ) ,
                    array(
                        'sExtends' => 'text',
                        'sButtonText' => '<i class="fa fa-circle-thin"></i>',
                        'sButtonClass' => 'reload_table_group btn-sm',
                        'fnClick' => "function(event){
            alert('ok');
        }",
                        'sToolTip' => trans("management-system::generals.buttons.reload") ,
                    ) ,
                    array(
                        'sExtends' => 'collection',
                        'sButtonText' => '<i class="fa fa-angle-double-down"></i>',
                        'sButtonClass' => 'btn-sm',
                        'aButtons' => array(
                            'xls',
                            'copy'
                        )
                    ) ,
                )
            ) ,
            'aoColumns' => array(
                array(

                    // 'sTitle' => trans('management-system::units.titles.id') ,
                    'sClass' => 'text-center',
                    'sWidth' => '30pt',
                    'aDataSort' => array(
                        0,
                        1
                    ) ,
                    'mData' => 'id'
                ) ,
                array(

                    // 'sTitle' => trans('management-system::units.titles.name') ,
                    'aDataSort' => array(
                        1,
                        0
                    ) ,
                    'mData' => 'name'
                ) ,
                array(

                    // 'sTitle' => trans('management-system::units.titles.short') ,
                    'mData' => 'code',
                ) ,
                array(

                    // 'sTitle' => trans('management-system::generals.titles.option') ,
                    'mData' => null,
                    'sClass' => 'text-center no-padding valign-middle',
                    'sWidth' => '60pt',
                    'bSearchable' => false,
                    'bSortable' => false,
                )
            ) ,
            'fnRowCallback' => 'function( nRow, aData, iDisplayIndex ) {
                $btn_edit = "<button class=\'btn btn-sm btn-default\' onclick=\'$(this).fnEdit(" + aData[\'id\'] + ")\'><i type=\'button\' class=\'glyphicon glyphicon-pencil text-green\' role=\'button\'></i></button>";
                $btn_delete = "<button class=\'btn btn-sm btn-default\' onclick=\'$(this).fnDelete(" + aData[\'id\'] + ")\'><i type=\'button\' class=\'glyphicon glyphicon-trash text-red\' role=\'button\'></i></button>";
                $("td:eq(3)", nRow).html($btn_edit + "&nbsp;" + $btn_delete);
            }',

            'infoCallback' => 'function( settings, start, end, max, total, pre ) {
                $(this).fnSetApi(this.api());
                $("td:eq(3)").
            }',

            'fnServerParams' => 'function(aoData) {
                aoData.push({name: "site_id", value: $(\'#site\').val()});
            }',
            'fnServerData' => "function ( sSource, aoData, fnCallback, oSettings ) {
        var oApi = this.oApi;
        aoData.push({ 'name': 'method', 'value': 'list'});
        oSettings.jqXHR = $.ajax( {
            'dataType': 'json',
            'type': 'POST',
            'url': sSource,
            'data': aoData,
            'success': fnCallback,
            'error': function( xhr, textStatus, error ){
              if ( textStatus === 'timeout' ) {
                alert( '" . trans('layout-master::datatables.titles.AjaxTimeOut') . "' );
              } else {
                alert( '" . trans('layout-master::datatables.titles.AjaxError') . "' );
              }
              oApi._fnProcessingDisplay( oSettings, false );
            },
            beforeSend: function(request) {
              return request.setRequestHeader('X-CSRF-Token', $(\"meta[name='csrf-token']\").attr('content'));
            }
        });
      }"
        ))->addColumn(array(
            trans('management-system::units.titles.id') ,
            trans('management-system::units.titles.name') ,
            trans('management-system::units.titles.short') ,
            trans('management-system::generals.titles.option') ,
        ))->setUrl(route('groups.postList'))->setId('table_group')->render();

Note: Can remove function 'setCallback' but i think use 'setCallback' still good and future will delete it!

opened by thienhung1989 10

Change row background color pdf export dataTable
need to check if the value of a column is negative and at the time of exporting to PDF, and if so, make that whole cell turn red or text-decoration: line-through.

However I do not know how to access the elements to make such a modification.

I can modify the background when it is being displayed in the browser but when exporting it does not work the same way.

{ extend: 'pdfHtml5', footer: true, text: 'PDF', header: true, title: t, orientation: 'landscape', exportOptions: { rows: function ( idx, data, node ) { //node.attributes.style = {background-color: "#000";}; //console.log(node.attributes.style); //return true; // return data[2] === 'London' ? // true : false; } }, customize: function(doc) {

doc.content.splice( 1, 0, { margin: [ 0, 0, 0, 0 ], alignment: 'center', image: base64 } ); doc.defaultStyle.fontSize = 10; doc.pageMargins = [20,10,10,10]; doc.styles.tableHeader.fontSize = 14; doc.styles.title.fontSize = 12; // Remove spaces around page title doc.content[0].text = doc.content[0].text.trim(); } }, { extend: 'print', footer: true, } ], fnRowCallback: function( nRow, aData, iDisplayIndex, iDisplayIndexFull ) { var valor = aData[3].split('$'); //console.log(teste); if (parseInt(valor[1])<0) { $(nRow).css('background-color', '#e96464') }else{ $(nRow).css('background-color', '#cdedc1') } }
opened by VitorBenjamin 0

Sorting arrows are not working

I have a modal that contains search button, when the user opens the modal and click search button, it calls an ajax to get data from the database in PHP page, it returns the data in table that will be loaded in ajax return success, I am using DataTable plugin to sort this table, but it isn't working,I may be missing something or put the function in wrong place,can anyone help me in that? HTML part

  <link href="../lib/css/theme.default.css" rel="stylesheet">
 <link rel="stylesheet" type="text/css" href="../lib/css/jquery.dataTables.min.css"/>
<script type="text/javascript" src="../lib/js/jquery.dataTables.min.js"></script> 
  <script type="text/javascript">
    $(function () 
      {
         $('#modal-3').on('shown.bs.modal', function (e) { 
            $('#SearchResults').tablesorter({
              widgets        : ['zebra', 'columns'],
              usNumberFormat : false,
              sortReset      : true,
              sortRestart    : true
           });
         })
      });
    </script>
  <body>
  <div class="modal fade" id="modal-3">
  <div class="modal-body"> 
    <div class="row">
      <table id="SearchResults" class="tablesorter" style="width:auto;"></table> 
    </div>
  </div>
  <div class="modal-footer">
    <button type="button" class="btn btn-success btn-md" style="margin:0;width: 75px;" onclick="SearchOrders()">Search</button>
  </div>
 </div>
</body>
</html>

Php page

      <?php

       include_once "connect.php";

       $output='';

       $bool_is_data_saved = false;

       $SDate=date('Y-m-d',strtotime($_POST["StartDate"]));
       $EDate=date('Y-m-d',strtotime($_POST["EndDate"]));

      $stmt ="SELECT DISTINCT Order_ID,Customer_ID,Required_Date,Delivery_Date,Order_Status FROM Orders where Required_Date between '".$SDate."' and '".$EDate."'";

      $row = $conn->query($stmt)->fetchall(PDO::FETCH_ASSOC);
      if (count($row) > 0)
      {
         $output.='<thead>
                <tr>
                    <th>Order No.</th>
                    <th >Customer Name</th>
                    <th>Order Date</th>
                    <th>Category Name</th>
                    <th>Category Type</th>
                    <th>Quantity in Kilos</th>
                    <th>Delivery Date</th>
                    <th>Order Status</th>
                </tr>
                </thead>
         ';
          foreach ($conn->query($stmt) as $rows) 
         {
         //Getting Customer Name
         $sql="SELECT first_name_,last_name_ FROM Customers where Cust_id='".$rows["Customer_ID"]."'";
         $result=$conn->query($sql)->fetch(PDO::FETCH_ASSOC);

        //Getting Order Data
        $query="SELECT distinct Order_ID,Category_ID,Product_ID,Amount FROM Order_Product where Order_ID='".$rows["Order_ID"]."'";

        foreach ($conn->query($query) as $results) 
        {
            $newsql="SELECT Category_Name from Categories where Category_ID='".$results['Category_ID']."'";
            $newresult=$conn->query($newsql)->fetch(PDO::FETCH_ASSOC);
            $CatName=$newresult['Category_Name'];

            $newsql="SELECT Product_Name from Products where Product_ID='".$results['Product_ID']."'";
            $newresult=$conn->query($newsql)->fetch(PDO::FETCH_ASSOC);
            $ProName=$newresult['Product_Name'];

            $output.='
            <tbody>
            <tr>
                <td>'.$rows['Order_ID'].'</td>
                <td>'.$result['first_name_'].' '.$result['last_name_'].'</td>
                <td>'.$rows['Required_Date'].'</td>
                <td>'.$CatName.'</td>
                <td>'.$ProName.'</td>
                <td>'.$results['Amount'].'</td>
                <td>'.$rows['Delivery_Date'].'</td>                
            ';
            $stmt = "SELECT distinct Status_ID,Status_Name FROM Order_Status WHERE Status_Name !='".$rows['Order_Status']."'";

            $output.='<td>';
            $output .= '<select name="comboBox" style="width: 100%;color: #ffffff;background-color: rgb(16,29,73);">';
            $output .= '<option value="'.$rows['Order_Status'] .'">'.$rows['Order_Status'].'</option>';

            foreach ($conn->query($stmt) as $res) 
            {  
                $output .= '<option value="'.$res['Status_ID'] .'">'.$res['Status_Name'].'</option>';    
            }
            $output .= '</select>';
                $output .= '</td>';
                $output .='</tr>';
        }
    }

    $output.='</tbody>';
    $bool_is_data_saved = true;
    echo $output;
    }

    if(!$bool_is_data_saved)
    {
       echo ("Failed");
    }
  ?>

SearchOrders Function `

  function SearchOrders()

    {

	document.getElementById("SearchResults").innerHTML="";

	document.getElementById("NoData").innerHTML="";

	document.getElementById("Results").style.display = 'none';

	startDate=document.getElementById("Start_Date").value;

	endDate=document.getElementById("End_Date").value;

	$.ajax({
		type:"POST",
		url:"searchorders.php",
		data:
			{ 
		       'StartDate': startDate, 
		       'EndDate':endDate
	      	},
		success: function(data)
		{
			if (data == "Failed")
			{
				$('#NoData').append("No data Found!");
			}
			else
			{
				$('#SearchResults').append(data);
				document.getElementById("Results").style.display = 'block';
                                    $('#SearchResults').DataTable({"columnDefs": [ {
                   "targets": [0,4,5,6],
                    "orderable": false
                  }]});
	
			}
		}
	})
}`

opened by BasmaNabil 0

How to make load data from server to datatable chumper faster ?

Now i'm working on load data and show to datatable.

but now i have 265 records but it's very slow.

I'm using MYSQL AURORA in AWS SERVER.

Thank you for answers

opened by userhuman 0
Fusing Chumper Collection with the Chunk/Paginate method

Hi team,

Is it possible to fuse chunk or pagination in a chumper datatable collection method to speed up the collection engine as the collection engine has more features than the query in case of usability.

Please let me know if its possible.

opened by sujit223 0
export csv feature not supproted in Chumper

Hi-every one as you guys know that there is a export option in data-table core api https://datatables.net/extensions/buttons/examples/html5/simple.html I want same functionality in Chumper but haven't found any option for export csv functionality please help me to figure out how to integrate these buttons in chumper Thanks in advance..

opened by tahseen2k8 0