chumper / datatable Goto Github PK

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

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:

http://i.imgur.com/VPXM7kW.png

how can I do that the no ordering colums not show the icons (up and down) on hover to set order??

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.

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]);
})

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

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?

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

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?

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

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.

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.

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 :)

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,
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 ! ;)

Is there a way to integrate this directly?

I'm calling $model->foo, doesn't return anything

https://github.com/ShawnMcCool/laravel-auto-presenter

Hello again!

Have any chances to have multi column filtering like this

http://www.datatables.net/examples/api/multi_filter.html

Thanks!

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.

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

Can you please update this package to support Laravel 4.1

Thanks

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

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.

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]

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!

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.

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

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?

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}}

When I use the example there is a warning message send with the json Response;

string 'select \* from "accounts"' (length=24)

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();

Getting ILIKE statements through doInternalSearch() on a MySQL Query.

:(

Please and thank you.

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.

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();

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!

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

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;
        })

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 ?

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. :)

How can I add an ID to a cell or set a class to it using the server side solution?

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();
    }

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

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() }
);
}

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! 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

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.

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.

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!

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 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 ?

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.