lankit/datatablesbundle

Symfony2 bundle for DataTables.js server-side processing when using Doctrine Entities

LanKitDatatablesBundle

This bundle provides server-side processing for DataTables.js when using Doctrine Entities. It works by parsing mData sent from DataTables.js and returning the corresponding data.

Documentation

Read the Documentation Here

Installation

Please see the documentation for installation instructions.

License

This bundle is released under the MIT license.

datatablesbundle's Issues

Doctrine softdeleteable extension does not work

it seems this method:

$datatable = $this->get('lankit_datatables')->getDatatable('Pm4CoreBundle:Country');

Does not work out of the box with:

https://github.com/l3pp4rd/DoctrineExtensions/blob/master/doc/softdeleteable.md

Doing a normal query with the entity manager does not returns softdeleted rows, but getDatatable returns them.

Is there a way to activate the doctrine listeners involved with this bundle?

Allow non mData joins to be pre-filtered

At the moment only joins / associations that are included in the mData can be pre filtered as only those associations are joined - i would like to pre filter on data that is not selected in the view

getCountAllResults throws QueryException when filtering result by joined column.

getCountAllResults throws QueryException when I'm using addWhereBuilderCallback method to filter result by joined column.

Query trying to be executed is:
"SELECT count(Purchases.id) FROM XXX\Entity\Purchase Purchases WHERE Purchases_Merchants.id = 18"

With hideFilteredCount(false) everything works fine.

Undefined Index when I use an Entity which it`s primary key is a foreign key

Hi!!

I´m using this bundle and i`m getting 'undefined index' error when I use an entity that has a primary key wich is a foreign key at the same time. This is my code:

public function getUserTableAction() {
$datatable = $this->get('lankit_datatables')->getDatatable('ErictelValetBundle:TDevices');
return $datatable->getSearchResults();
}

And this are the associations of entity TDevices:

Erictel\ValetBundle\Entity\TDevices:
type: entity
table: t_devices
id:
deviceInfo:
associationKey: true
fields:
jid:
type: string
length: 45
fixed: false
nullable: false
oneToOne:
deviceInfo:
targetEntity: TDeviceInfo
cascade: { }
mappedBy: null
inversedBy: null
joinColumns:
device_info_id:
referencedColumnName: id
orphanRemoval: false
manyToOne:
state:
targetEntity: TStates
cascade: { }
mappedBy: null
inversedBy: null
joinColumns:
state_id:
referencedColumnName: id
orphanRemoval: false
user:
targetEntity: TUsers
cascade: { }
mappedBy: null
inversedBy: null
joinColumns:
user_id:
referencedColumnName: id
orphanRemoval: false
serviceIsActive:
targetEntity: TServiceStateCodes
cascade: { }
mappedBy: null
inversedBy: null
joinColumns:
service_id:
referencedColumnName: id
orphanRemoval: false
lifecycleCallbacks: { }

And this is the error:

Notice: Undefined index: TDevices in /var/www/valet_parking/vendor/lankit/datatables-bundle/LanKit/DatatablesBundle/Datatables/Datatable.php line 562.

Can anybody help me??

Thanks in advance

A few troubles with a collection field

Hi Chad,

I got 2 problems trying to list an entity with a many to many, or one to many relation.

First thing : let's say i have a customer who can have orders. If i want to list my customers and their orders, according to http://datatables.net/ref#mRender
i'll have to do something like that :

"aoColumns": [
      { "mData": "id" },
      { "mData": "name" },
      {
        "mData": "orders",
        "mRender": "[, ].id"
      }
    ]

But with the bundle, "orders" is not recognize as an association :

Field 'Orders' not found
Stack Trace
in ...../vendor/lankit/datatables-bundle/LanKit/DatatablesBundle/Datatables/Datatable.php at line 298

So i'll have to use "orders.id" instead, but then datatables won't be able to use the mRender collection notation because in the json, there is no "orders.id"

"orders":[{"id":1},{"id":2}]

So i'll have to do something like that (have to use "full" because "data" is empty) :

"mRender": function ( data, type, full ) {
                    var text = "";
                    for(index in full.orders) {
                        if(full.orders[index].id != undefined ) {
                            text += full.orders[index].id + ", ";
                        }
                    }
                    return text.substring(0, text.length -2 );

                }

It's not that important as we can find an alternative way to do what we want, but maybe you can add an option so we can say when a field is a actually an association ?

Second thing is more annoying, when i got my list, the "iTotalDisplayRecords" seems to count the "orders" instead of the "customers"

Once again, thanks for your work :)

get Value of an entity has been associated from...

Hi All!

I'm using an entity to make a table with this bundle, that ID it's associated to different entities and I want to now how it's possible to reach that values from mData.

The ID it's de primary key value from this entity, but it's settled to other entitites, OneToOne unidirectional or bidirectional?

Thanks in advance,
Carl

Fatal error: Class 'LanKit\DatatablesBundle\LanKit\DatatablesBundle\DependencyInjection\LanKitDatatablesExtension' not found

Hello,

I just installed your bundle by following the documentation. Everything went fine with composer, then i registered the bundle in appKernel, then created a new action in a controller and then checked in frontend and bam ! I got a fatal error :

Fatal error: Class 'LanKit\DatatablesBundle\LanKit\DatatablesBundle\DependencyInjection\LanKitDatatablesExtension' not found in /var/www/xxx/vendor/lankit/datatables-bundle/LanKit/DatatablesBundle/LanKitDatatablesBundle.php on line 12

Do i miss something ? Sorry if i did something stupid, and for my bad english ^^

Composer update required "doctrine/doctrine-bundle": "1.1.*"

My composer update error message:

lankit/datatables-bundle dev-master requires doctrine/doctrine-bundle 1.0.* -> satisfiable by doctrine/doctrine-bundle v1.0.0.

Can only install one of: doctrine/doctrine-bundle v1.1.0, doctrine/doctrine-bundle v1.0.0.
Installation request for lankit/datatables-bundle dev-master -> satisfiable by lankit/datatables-bundle dev-master.

DatatablesBundle & Symfony 2.3.*

Hey :)

Is there an DatatablesBundle update planed ? I can't intall on Symfony 2.3.0 :/

Thanks,
Litz.

SQL Error - The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified

Using the latest commit 08f5972 there is an issue, receive the following error

[2/2] DBALException: An exception occurred while executing 'SELECT DISTINCT TOP 10 id0 FROM (SELECT s0_.id AS id0, s0_.quote_ref AS quote_ref1, s0_.label AS label2, s0_.created AS created3, s1_.id AS id4, s1_.default_uplift AS default_uplift5 FROM sms_quote s0_ INNER JOIN sms_tariff s1_ ON s0_.tariff_id = s1_.id WHERE s0_.is_active = 1 ORDER BY s0_.created DESC) dctrn_result':

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.  -+
[1/2] PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.  -

Support DT_RowId and DT_RowClass

Per the example on the site...

http://datatables.net/release-datatables/examples/server_side/select_rows.html

Forced camelcase in joined entities

I would love to have option of configuration if i use camelcase strategy or underscore. Refers to:

• /vendor/lankit/datatables-bundle/LanKit/DatatablesBundle/Datatables/Datatable.php

$joinName .= '_' . $this->getJoinName(
                    $metadata,
                    Container::camelize($metadata->getTableName()),
                    $entityName
                );

getDataTables Doens't return anything

Hi all, I have Symfony2 with this bundle to get information from 3 entities. When I go to the table view anything appears and I get a 500 error. Making error_logs until I found the 'problem'.

The function 'new DataTable' doesn't return anything and I think it's problem from the serializer.

I put this issue in stackoverflow, you can see all code there:

http://stackoverflow.com/questions/38634935/lankit-datatables-bundle-in-symfony2

Thanks in advance,
Carl

Isn't defining the fields in "aoColumns" a big security issue?

Hi,

I'm new to DataTables and this bundle (and GitHub, in fact; I registered to ask this question!), so apologies if this has been addressed already, but I can't seem to get my head around one thing... Isn't defining the columns in the actual request a big security issue?

I don't know if this is a problem with DataTables itself, or just this bundle, but from following the documentation, it looks like the only way to define the fields to query is for it to be done in the actual public page's code, as detailed here:

    "aoColumns": [
        { "mData": "id" },
        { "mData": "description" },
        { "mData": "customer.firstName" },
        { "mData": "customer.lastName" },
        { "mData": "customer.location.address" }
    ]

To begin with, I dislike having such a thing visible to the public, as it unnecessarily exposes the inner workings of the data. But even more importantly, there's nothing stopping anyone altering the request to get different fields, many of which could contain sensitive data, like passwords (hashed of course!).

Am I really missing something here? I don't understand how such a monumental security flaw like this hasn't already been mentioned, let alone even be possible in the first place, and nothing about is mentioned in the documentation at all.

I would definitely prefer a way to define my fields server-side instead (in my case, in the controller during the creation/retrieval of the datatable itself). Can this be done? I don't want the request to be able to make such a decision! Ever!

Thanks very much,

Maeldor

Add Ability to Add a Custom Entity Filter

In many cases someone would probably want to filter which entities to work with prior to processing the DataTables request. Still need to think of the best way to achieve this.

Adding ability to view Date objects

How do you make it work with database objects like viewing dates, because right now it shows "[object Object]" if i try to display a date

How to render row even if joined entity is null

From bundle README example:

"aoColumns": [
{ "mData": "id" },
{ "mData": "description" },
{ "mData": "customer.firstName" },
{ "mData": "customer.lastName" },
{ "mData": "customer.location.address" }
]

let's say i got 2 entities one with customer join set , and other one with customer being null. In this case datatable will render only one row (as i want it to render firstName, lastName, etc). How to manage this if i want both rows to be shown?

Pre-Filtering Search Results : display issue ?

Hello,
When i'm using the "Pre-Filtering" function (ex: i want all "active" customers), when i got my results in the datatable, it say "filtered from xxx total entries"

Maybe i got something wrong but if i use a "pre-filter" i'd like not to know the list was filtered ?

I made a quick fix in the "getCountAllResults" function, i can make a pull request but maybe it's a wanted behaviour ?

Thanks for your time :)

Documentation for adding a calculated field

Hi,
is it possible to add documentation for how to add calculated fields.

In my case, I have a oneToMany relationship and i want to know how much row has the entity.

For exemple :

$datatable = $this->get('lankit_datatables')->getDatatable('WaldoMyBundle:Customer');
$datatable->setSelect("COUNT(Customer.shoppingCart) as nbShoppingCart");
        return $datatable->getSearchResults();

Thanks

Reorganize the Datatable Class

The Datatable class should be broken down into several separate classes. So far I'm thinking one to represent the DataTables.js request options, a class to represent individual mData columns, and one to represent the creation/building of the actual QueryBuilder object to construct the search. Also, the DatatableManager class should probably be moved to a separate directory.

Notice: Undefined index: iColumns

Hi,

when i try to call response action i get the

Notice: Undefined index: iColumns in [...]/vendor/lankit/datatables-bundle/LanKit/DatatablesBundle/Datatables/Datatable.php line 271

and no matter if i pass the iColumns parameter in my ajax call or not. Here is my setup:

• Html

<table class="table table-striped table-bordered table-hover" id="sequences_datatable">
            <thead>
                <tr>
                    <th>Id</th>                                            
                    <th>Name</th>    
                    <th>Created By</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>Row 1 Data 1</td>
                    <td>Row 1 Data 2</td>
                    <td>Row 1 Data 3</td>
                </tr>
            </tbody>
        </table>

• JS

$(function(){
       $('#sequences_datatable').dataTable({
           "processing": true,
           "serverSide": true,
           "stateSave": true,  
           "aoColumns": [
                { "mData": "sequence.id" },
                { "mData": "sequence.name" },
                { "mData": "sequence.created_by.username" }
            ],
           'ajax' : '{{path('ajax_sequences_list')}}',
       }); 
    });

• Controller Action (default from documentation)

    /**
     * @Route("/sequences/list", name="ajax_sequences_list")
     * 
     * @Template()
     */
    public function sequencesListAction(Request $request) {
        $datatable = $this->get('lankit_datatables')->getDatatable('BoostitWebBundle:Sequence');

        return $datatable->getSearchResults();
    }

Would appreciate any help