chumper / datatable Goto Github PK
View Code? Open in Web Editor NEWThis is a laravel 4 package for the server and client side of datatables at http://datatables.net/
Home Page: https://github.com/Chumper/Datatable
This is a laravel 4 package for the server and client side of datatables at http://datatables.net/
Home Page: https://github.com/Chumper/Datatable
Hi,
you've built a great package, thank you for that !
I'm moving to Dynatable.js (http://os.alfajango.com/dynatable) because I prefer its configuration (so much easier than datatables !), do you think that you can add the compatibility to Dynatable ?
I think the processes on the server-side are really close. Maybe I could fork your repo to create a Dynatable package, but I'm a beginner with Laravel...
Thanks again for this great package ! ;)
Am running 2.2 but am unable to load the js files at the bottom of my page. When I put them in all works fine. It looks like this was something that you fixed a while ago, or am I wrong? I have only just started playing with Datatable. It looks like a great alternative to Datatables.
Hi,
it's a good practise to put all the javascript just before the < / body> tag.
The problem is that when you call the Datatable::table() function, the script is put right after the table, and Jquery isn't declared yet.
In my opinion, we should be able to have a separate function like Datatable::script() to be able to append the script wherever we want. (Still after the Datatable::table() declaration, but not right after)
I see several options to do it :
Datatable::table()->noScript() which don't include the script part
Datatable::script() to append the script
I currently get a "Uncaught ReferenceError: jQuery is not defined" when using this package. I load jquery on the bottom of my page, so this makes sense I guess, but this should be fixed.
Is there a way to integrate this directly?
I'm calling $model->foo, doesn't return anything
Hello again!
Have any chances to have multi column filtering like this
http://www.datatables.net/examples/api/multi_filter.html
Thanks!
Hello Nils,
I am experiencing weird behavior with the plugin. I have two pages worth of data (with showing 15 per page)
this is the first page of the data:
then when going to the second page I am getting:
(notice the 0151)
When this happens, for instance I see the data gets "jumbled" up I see the same rows from the first page on the second page even though I am using a orderBy clause in my query.
The third button as well can not be clicked. The cursor changes, but nothing happens when page 3 is requested.
{{ Datatable::table()
->setURL(route('allApplicationsJson'))
->addColumn('', 'Recieved', 'Status', 'First Name', 'Last Name', 'Student ID', 'Aid Year', 'Type')
->setOptions('bProcessing', true)
->setOptions('bSort', false)
->setOptions('iDisplayLength', '15')
->setOptions('aLengthMenu', [5, 10, 15, 20, 25, 30, 35, 40, 45, 50])
->setOptions('bAutoWidth', false)
->setOptions('aoColumns', array(
array('sWidth' => '1%'),
array('sWidth' => '1%'),
array('sWidth' => '5%'),
array('sWidth' => '5%'),
array('sWidth' => '1%'),
array('sWidth' => '1%'),
array('sWidth' => '2%'),
array('sWidth' => '1%'),
))
->render() }}
I don't really know where to go from here to debug this issue I am having. Any tips? I will continue to play around with this hopefully it is something small. - I am loving the plug in. Thanks in advanced.
Can you please update this package to support Laravel 4.1
Thanks
It would be perfect if we could search for multiple words with serverside processing like this: http://datatables.net/forums/discussion/comment/45442
What do you think?
I'm trying to use your setOptions to set the bSortable to false for the 5th column.
I've tried various ways to set this using setOptions but it doesn't seem to work.
I hard coded the option into the template.blade.php just to check to see if it work which it did.
Here is the jQuery Datatables option setting:
"aoColumnDefs": [
{ "bSortable": false, "aTargets": [4] }
]
How do I set this option using your method?
Unfortunately PHP can't create an object on the fly.
Do I need to create the object in a separate line of code and pass it in that way?
-- Christopher Mullins
I have a model that has a column called "ass_active", it's using a connection called "mysql_superdepot".
Here is what I'm building prior to handing it off to Datatables:
$model = SuperDepotSuperfund::select(array('superfundname', 'datesold'))->where('clientid', '=', $id); if (Input::has('status')) { if (Input::get('status') == 'active') { $model = $model->where('ass_active', '=', 'True'); } if (Input::get('status') == 'inactive') { $model = $model->where('ass_active', '!=', 'True'); } if (Input::get('status') == 'terminated') { $model = $model->where('ass_terminated', '=', 'True'); } }
If I call return $model->get();
- it works and returns the normal json model no probs. However if I call:
return Datatables::of($model)->make();
It throws the following error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ass_active' in 'where clause' (SQL: select count(*) as aggregate from (select `superfundname`, `datesold` from `tblclientsuperfunds` where `clientid` = 13412 and `ass_active` = True) AS count_row_table)'`
Any ideas?
I think it might be related to the fact it's not using the default connection as I have another table in the database with the same name, but using the default connection.
nice package how can we add custom column in it, for example i have 2 column coming from database id, name and want to add third custom column, action which will has anchor edit.
some more other features i don't want to use script from your package just generate only json settings for datatables which i can use in my javascript
Hi, a join query return a null count on paginate : "iTotalRecords":null,"iTotalDisplayRecords":null
1st page is ok but other ones return null values.
No problem with a simple select query.
When I add at controller
->setAliasMapping();
I see this error on render:
DataTables warning (table id = 'DataTables_Table_0'): Requested unknown parameter '0' from the data source for row 0
It looks like the Laravel forum thread is no longer active, so am asking here. This is quite a great plug-in you've made. I'm wondering how I might use a DB:raw call in the select with a group by, like below. It is currently returning a 500 error:
$query = $this->user
->select('users.id', 'users.username','users.email', DB::raw('GROUP_CONCAT( roles.name) AS roles'), 'users.confirmed', 'users.created_at')
->leftjoin('assigned_roles', 'assigned_roles.user_id', '=', 'users.id')
->leftjoin('roles', 'roles.id', '=', 'assigned_roles.role_id')
->groupBy('users.id')
->get();
$table = Datatable::collection($query
return $table
->showColumns('id', 'username', 'email','roles')
->setSearchWithAlias()
->searchColumns("roles") ...
UPDATE: I think it's better to do this query without group by and instead get the roles per user via something like
->addColumn('roles', function($model) { return $model->currentRoleIds(); })
But are DB::raw() queries allowed?
One other minor question -- I notice that after setting orderColumns, the columns that don't sort still show the asc/desc arrow images, and the arrows are still selectable even though they don't do anything. Is this intended?
Hello Chumper,
I am currently trying to create CRUD operations within the datatable using your library.
I have a actions Column that will hold a user-id (for example) how do I create links within the datatable? Could you help point me in the right direction?
Actions column:
Hello Chumper,
I wanted to know if you package has the capablity to allow for exact word matches? For example I search Complete but Incomplete and Complete will be shown from the search. Is it possible for just Complete to be shown after a search?
I have in my eloquent result a column created by a DB:raw like this:
$result = Atendimento::Join('cliente', 'atendimento.a_cliente_id', '=', 'cliente.c_id')
->leftJoin('telefones', 'cliente.c_id', '=', 'telefones.t_id_cliente')
->leftJoin('usuario', 'a_corretor_id', '=', 'usuario.id')
->leftJoin('repiques_favoritos', 'a_id', '=', 'repf_id_atendimento')
->leftJoin('cliente_imovel', 'a_cliente_id', '=', 'imob_cliente_id')
->leftJoin('imob_imoveis', 'imob_imovel_id', '=', 'imovel_id')
->leftJoin('imovel', 'imob_imoveis.imovel_id', '=', 'imovel.imovel_codigo')
->groupBy('a_id')
->Select('a_id', 'a_updated_at', 'c_nome', 't_cod_area', 't_telefone', 'usuario.nome', 'imovel.imovel_nome', 'imovel.imovel_bairro',
DB::Raw('(SELECT COUNT(r_id_atendimento) FROM atendimento_repique WHERE r_id_atendimento = a_id ) AS Total'), 'c_id')
->whereRaw('(repf_tipo <> 3 OR repf_tipo is null)');
If I try to filter or search by the "Total" (Yes I know O need a having clause to make this filter) It's not work.
Can you implement it or can you give me the steps where I need to modify to have this search work?
Hello,
First I would like to thank you for this great job!
But I have a question: Is there any way to change the class of the element table.
example:
Thank you for your attention!
Hello! First off, thanks you for coding this awesome tool!
Greetings from Chile, so excuse my bad english.
Well, I'm working with Query Engine with 50.000 data.
It works well, except that in search mode. It shows:
"Showing entries from 21 to 30 from a total of 50,000 entries"
It should show:
"Showing entries from 21 to 30 from a total of 47 entries
(filtered from a total of 50000 entries)"
I have made my own solution. In QueryEngine.php class I added this new variable:
private $counter = 0;
Then i rewrote the function count()
public function count()
{
return $this->counter;
}
And last i added the following line inside compile() function (at top of code):
$this->counter = $builder->count();
Thanks
When I use the example there is a warning message send with the json Response;
string 'select \* from "accounts"' (length=24){"aaData":[["1","109411"],["2","134088"],["3","127194"],["4","136775"],["5","100849"],["6","136932"],["7","117070"],["8","135844"],["9","106640"],["10","105231"],["11","134366"],["12","119389"],["13","137035"],["14","120082"],["15","126106"],["16","137951"],["17","132864"],["18","129717"],["19","114318"],["20","123645"],["21","133949"],["22","108770"],["23","134096"],["24","131160"],["25","137027"],["26","137455"],["27","105713"],["28","106074"],["29","107168"],["30","107092"],["31","105671"],["32","137971"],["33","136614"],["34","114753"],["35","132397"],["36","130927"],["37","120823"],["38","130378"],["39","134395"],["40","119698"],["41","106147"],["42","105987"],["43","138056"],["44","112951"],["45","130839"],["46","104996"],["47","133153"],["48","125909"],["49","105213"],["50","109776"],["51","111832"],["52","128303"],["53","133842"],["54","126889"],["55","106229"],["56","126195"],["57","113879"],["58","134230"],["59","137941"],["60","123114"],["61","129734"],["62","130801"],["63","134730"],["64","106007"],["65","114383"],["66","113570"],["67","104859"],["68","126259"],["69","104208"],["70","134791"],["71","140941"],["72","133844"],["73","128676"],["74","120179"],["75","113942"],["76","114651"],["77","104664"],["78","104132"],["79","915462"],["80","123164"],["81","128039"],["82","135366"],["83","129796"],["84","126884"],["85","127631"],["86","124756"],["87","132720"],["88","119599"],["89","123333"],["90","137952"],["91","106254"],["92","131219"],["93","114091"],["94","121367"],["95","125684"],["96","104082"],["97","138844"],["98","133653"],["99","114609"],["100","139527"]],"sEcho":0,"iTotalRecords":100,"iTotalDisplayRecords":100}
When you use the query() method it ignores any search and order functionality entered by the user on the front-end.
This is because you need to re-assign the builder when adding new database queries to it.
E.g.
In QueryEngine.php line 110 change:
$this->builder->orWhere($c,'like','%'.$this->search.'%');
to:
$this->builder = $this->builder->orWhere($c,'like','%'.$this->search.'%');
Alternatively, in recent versions of Laravel you can avoid the need to constantly re-assign by initially assigning the builder property with a ->query() method on the chain. I'm not sure when this was introduced, so I don't know about backwards-compatibility.
E.g.
In QueryEngine.php line 35 change to:
$this->builder = $builder->query();
on line 226 you have:
if($i === $this->orderColumn)
looks like $this->orderColumn isn't being cast as an int.
fix is to go:
$this->orderColumn = (int) $column;
on line 60 of QueryEngine.php
Have any chances to retrieve the last query with filters and order?
I tryed
$queries = DB::getQueryLog();
$last_query = end($queries);
but this returning an empty array.
Thanks.
how can I do that the no ordering colums not show the icons (up and down) on hover to set order??
Minor Bug: Generation of Javascript is incorrect for the following option:
Option:
->setOptions('aaSorting', array(
array(
1 => 'asc'
)
))
Should generate an Array of Arrays:
"aaSorting": [[1, 'asc']]
But instead generates an Array of Objects:
"aaSorting": [{'1': 'asc'}]
-- Christopher Mullins
just got this ErrorException after updating to 2.1.x:
exception 'ErrorException' with message
'Non-static method Chumper\Datatable\Datatable::collection() should not be called statically, assuming $this from incompatible context'
return Datatable::collection(User::all())
->showColumns('user_id', 'email', 'login_locked', 'confirmed', 'actions')
->addColumn('user_id', function($model) {
return $model->getPresenter()->id;
})
->addColumn('email', function($model) {
return $model->getPresenter()->email;
})
->addColumn('login_locked', function($model) {
return ($model->getPresenter()->is_locked) ? Lang::get('general.yes') : Lang::get('general.no');
})
->addColumn('confirmed', function($model) {
return ($model->getPresenter()->is_confirmed) ? Lang::get('general.yes') : Lang::get('general.no');
})
->addColumn('actions', function($model) {
return '<div class="btn-toolbar btn-group-sm">' . $model->getPresenter()->edit_link.$model->getPresenter()->login_link.'</div>';
})
->orderColumns('id', 'email', 'login_locked', 'confirmed')
->make();
rolling back to 2.0.x "solves" the issue.
Hi Chumper,
After comoser update I get the error below:
(I'm on PHP 5.3)
{"error":{"type":"Symfony\Component\Debug\Exception\FatalErrorException","message":"Cannot access protected property Chumper\Datatable\Engines\CollectionEngine::$rowClass","file":"C:\Users\Pieter\Documents\My Dropbox\Websites\www\laravel.dev\datatables2\vendor\chumper\datatable\src\Chumper\Datatable\Engines\CollectionEngine.php","line":234}}
How can I add an ID to a cell or set a class to it using the server side solution?
Thanks for the awesome plugin . can you please guide me how can i pass a additional parameter from client side and get in server side
for example in client site I want to pass this value in a ajax request so can i get in server side
fnServerParams": function (aoData) {
aoData.push(
{ "name": "firstcriteria", "value": $("#txtParameter1").val() },
{ "name": "secondcriteria", "value": $("#txtParameter2").val() }
);
}
Getting ILIKE statements through doInternalSearch() on a MySQL Query.
:(
Please and thank you.
Is there a way to order on relationships?
return \Datatable::from($this->job->query())
->showColumns('title', 'freelance', 'budget')
->addColumn('category',function($model)
{
return $model->category->name;
})
->searchColumns('title')
->orderColumns('title', 'category', 'budget')
->make();
When I order on category it says the column doesn't exist on jobs table.
When adding a column via addColumn() and placing that column in orderColumns()
The sort does not strip HTML or obey sTypes.
EG:
->addColumn('title', function($row)
{
return link_to_route('admin.organisations.show', $row->title, ['organisations' => $row->id]);
})
Heya!
I'm defining an accessor on one of my date columns to reformat it to UK format:
public function getInvoicedateAttribute($invoicedate) { if ($invoicedate != '') { return Carbon::createFromFormat('Y-m-d', $invoicedate)->format('d/m/Y'); } else { return ''; } }
The problem I'm having is when I use the following:
return Datatable::collection(Invoice::where('clientid', '=', $id)->get()) ->showColumns('invoicenumber', 'cstname', 'invoicedate') ->addColumn('status', function ($model) { if ($model->paidinfull == 'True') { return "<label class='label label-success'>Paid</label>"; } else { return "<label class='label label-danger'>Outstanding</label>"; } }) ->addColumn('actions', function ($model) { return "<a ui-sref='client.invoice-edit({invoicenumber:" . $model->invoicenumber . "})' class='btn btn-primary btn-xs' title='Edit invoice'><i class='fa fa-edit'></i></a> <a href='javascript:void(0);' opendialog url='" . Config::get('angularjsvars.partialsUrl') . "invoices/partials/reprint-invoice.html' update-id='" . $model->nvoicenumber . "' class='btn btn-info btn-xs' title='Reprint invoice'><i class='fa fa-print'></i></a>"; }) ->searchColumns('invoicenumber', 'cstname', 'invoicedate') ->orderColumns('invoicenumber', 'cstname', 'invoicedate') ->make();
It won't sort correctly. If I remove the accessor it works properly but the date format is back to being mysql date format.
Any ideas?
I'm using 2.0.x as 2.1.x has some issues. Many thanks :)
I only get that on my production server, not locally. And I have loads of other packages installed that works well. Servers are nearly identical, and I've done the usual tricks clearing composer cache and what not.
Hello,
I am really liked this bundle, and work with it in a large CRM System.
My question is: Have any chance to set the table id?
Like
<table id="myTable">
Thanks!
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
Does Datatables Plugin handle this on the server side?
I've set the option bCaseInsensitive for Datatables but it only seems to affect the first word of every column and not the entire content.
jQuery(document).ready(function(){
// dynamic table
jQuery('.IgflooDr').dataTable({
"sPaginationType": "full_numbers",
"bProcessing": false,
"bJQueryUI": false,
"oSearch": {"bCaseInsensitive":true},
"aaSorting": [[1,"asc"]],
"aoColumnDefs": [{"bSortable":false,"aTargets":[0,5,6]},{"sClass":"center","aTargets":[0,5,6]}, "sClass":"nowrap","aTargets":[1,4]}],
"bStateSave": true,
"sAjaxSource": "http:\/\/versicatalog.herokuapp.com\/account\/catalog\/api",
"bServerSide": true,
});
// custom values are available via $values array
});
-- Christopher Mullins
Hi Chumper,
I think you forgot to return the instance of the QueryEngine class in the setSearchOperator method so it breaks method chaining.
public function setSearchOperator($value = "LIKE")
{
$this->options['searchOperator'] = $value;
return $this;
}
Keep up the good work. :)
Hi,
It looks like setting the option
"bSearchable" => false
does not have any effect. Setting
"bSortable" => false
does work as expected, though. Is this a bug of am I overlooking something?
Thanks for your help!
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.
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();
}
Time for a fix maybe ? This is pretty annoying, while your example in the documentation shows you can show the date-time-stamps.
I developed this package because i was not happy with the only existing package at https://github.com/bllim/laravel4-datatables-package so i developed this package with in my opinion is superior.
Well... this output seems not superior at all to me :-)
ID Email Created At Updated At
2 [email protected] [object Object] [object Object]
17 [email protected] [object Object] [object Object]
Heya,
Thanks for the package! Getting an error when doing the following:
return Datatable::collection(ClientType::all(array('id', 'type'))) ->showColumns('id', 'type') ->searchColumns('type') ->orderColumns('id', 'type') ->make();
I get:
Symfony \ Component \ Debug \ Exception \ FatalErrorException Using $this when not in object context
It's highlighted line 217.
if(!is_null($this->rowClass) && is_callable($this->rowClass))
Update: 2.0.x works no probs.
Any ides why this would happen?
I have at server side
return Datatable::collection(Mymodel::orderBy('type')->get())
->showColumns('id','type','order','title','about')
->searchColumns('title','about')
->orderColumns('type','order','title')
->addColumn('type',function($model)
{
return Mymodel::Typename($model->type); //show the name of type number
})->make()
I want the first ordering default be 'type', but if y show 'type' like a custom field the order (in this example), the order default is by 'id', in fact, if I debug, the getOrder() return
["id","type","order","title","about"]
How can I establish a default order ?
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.
Hi yo.
leftJoins seem to get messed about in PHP 5.5. Something in the count() method is messing with the results. Overwriting joined ambiguous columns.
Fix seems to be changing in QueryEngine.php:
private function compile($builder, $columns)
{
$this->counter = $builder->count();
to
private function compile($builder, $columns)
{
$this->counter = $this->builder->count();
In a Users list, where users have many roles, I'd like to list all of those roles for each user. This works fine, but does Datatable give me another option?
->addColumn('roles', function($model) {
$roles = '';
$model->roles->each(function($role) use (&$roles)
{
$roles .= $role->name . '<br>';
});
return $roles;
})
Has this method been removed from the query engine for some reason. I'm testing with both MySQL and Postgres databases. When I tried to use this method with Datatable::query() I got a missing method error.
I searched the code for this method and could not find it either.
-- Christopher Mullins
Hi,
when I try to search on a float column value, the query fails because of the "LIKE" statement.
Maybe we could CAST the integer and float value to be able to use the search feature on them, but it supposes that we have to indicate the column type somewhere.
What do you think ?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.