waldo2188/datatablebundle

Symfony2 Ajax Datatable Bundle to simplify the use of http://datatables.net/ (and Doctrine entity inside)

datatablebundle's Issues

Problem sort column with method "setMultiple()"

When using the method setMutiple() , column sorting do not work properly .

The sorting is done on the next column.

Is there a solution ?

Thank you

Individual column searching subquery syntax error

I tested your bundle with this code:

return $this->get('datatable')->setEntity('CRMBundle:Company', 'x')->setFields([
            'name' => 'x.name',
            'street' => 'x.street',
            "surname"         => '(SELECT MIN(u.surname) FROM CRMBundle:Contact u WHERE u.company = x.id) as surname',
            "_identifier_"  => 'x.id'
        ])->setSearch(true)->setSearchFields(array(0, 2))->setGlobalSearch(true);

and if i tried to filtering by surname I got this error:
[Syntax Error] line 0, col 164: Error: Expected =, <, <=, <>, >, >=, !=, got 'LIKE'
The QueryException:
SELECT COUNT(x.id) FROM CRMBundle:Company x WHERE (SELECT MIN(u_840532410.surname) FROM CRMBundle:Contact u_840532410 WHERE u_840532410.company = x.id) LIKE :sSearch_2 AND (x.name LIKE :sSearch_global_0 OR x.street LIKE :sSearch_global_1 OR (SELECT MIN(u_840532410.surname) FROM CRMBundle:Contact u_840532410 WHERE u_840532410.company = x.id) = :sSearch_global_2)

Individual column searching (select inputs)

Hi, is it possible to change input text in individual column searching for select like in this example:
https://datatables.net/examples/api/multi_filter_select.html ?

Datatable editor

Hello, I am using your DatatableBundle and I want to be able to edit it, I would appreciate any help
Thank you

Maximum number of datatables?

I am currently using three datatables.
When I want to create a fourth, it takes over the settings + header of the third datatable.

Support for Responsive extension

Hi,

I'm trying to make the responsive extension work but I'm having a javascript error when activating the responsive trigger either through class or using the constructor approach as instructed on the responsive extension guide. Does this bundle support this.

Here is the js error:
TypeError: s is null

Route parameters on setMultiple

Hi,

I can not pass parameters to the route on function setMultiple.

I'm trying:

//...
->setMultiple(
      array(
            'delete' => array(
                  'title' => 'Delete',
                  'route' => [
                        'my_route' => [
                              'id' => 1
                        ]
                  ] 
            )
      )
)
//...

But I get:

An exception has been thrown during the rendering of a template ("Notice: Array to string conversion") in WaldoDatatableBundle:Snippet:multipleRaw.js.twig at line 2.`

Could you give me an example of how to do it?

thanks a lot

extend row-rendering (add class and data-id)

Hi,
I use DataTables and combined it with "open dialog on doubleclick". Because of lots of Entities (>20k) I switched to your Bundle. Everything works fine but I have to extend the of each row with css classes and a data-id property.

Old Code:
`{% for h in entities %}

{{ h.name }}
{{ h.description }}

{% endfor %}

<script> $(document).on("dblclick", ".opdia", function () { var editId = $(this).data('id'); var res = ""; var success = false; var url = "XXXXXXXXXX?xid=" + editId; $.when( $.ajax({ type: 'GET', url: url, success: function (response) { res = response; success = true; }, error: function () { //handle error }, })).then(function () { if (success) { $("#myModal").html(res).modal("show"); } }); }); </script>

`

Is it possible to customize the row-rendering?

Issues when search with aggregation SQL

When i try to enable search i have this problems
`
An exception occurred while executing 'SELECT COUNT(DISTINCT s0_.id) AS sclr_0 FROM shipment s0_ LEFT JOIN delivery_options d1_ ON s0_.deliveryoption_id = d1_.id LEFT JOIN address_book a2_ ON s0_.sender_addressbook_id = a2_.id LEFT JOIN address_book a3_ ON s0_.recipient_addressbook_id = a3_.id LEFT JOIN package p4_ ON s0_.id = p4_.shipment_id LEFT JOIN shipment_status s5_ ON s0_.shipment_status_id = s5_.id WHERE s0_.shipment_number LIKE ? OR s0_.control_number LIKE ? OR s0_.service_type LIKE ? OR d1_.delivery_option LIKE ? OR a2_.first_name LIKE ? OR a3_.first_name LIKE ? OR COUNT(p4.id) LIKE ?_ OR s0_.chargeable_weight LIKE ? OR s0_.grand_total LIKE ? OR s5_.name LIKE ? OR s0_.id LIKE ?' with params ["%%", "%%", "%%", "%%", "%%", "%%", "%%", "%%", "%%", "%%", "%%"]:

SQLSTATE[HY000]: General error: 1111 Invalid use of group function
`

As I see that the sql script is generated is wrong when have aggregation. It should be
Having COUNT(p4_.id) LIKE ? ...

Filtering / Searching / order : nothing works

I am using Symfony 3.1.3 and Waldo 4.0.2 and filtering / sorting /order, brief, all ajax callings after first display of DataTable still return the exactly same JSON array.

It appears as Waldo DatatableBundle ignores parameters in request.

Someone had the same issue ?

I followed git instructions.

Renderer and global search

Is it possible that the global search is not working on columns where I use a renderer?
This is my Code:

`$query = $this->get('datatable')
->setEntity("AppBundle:Logs", "lg")
->setFields(
array(
"Modul" => 'lg.module',
"Log" => 'lg.id',
"Datum" => 'lg.when', // Declaration for fields:
"Von" => 'lg.id', // "label" => "alias.field_attribute_for_dql"
"identifier" => 'lg.id') // you have to put the identifier field without label. Do not replace the "identifier"
)

                ->setWhere(                                                     // set your dql where statement
                     'lg.domain = :domain',
                     array('domain' => 'log')
                )
                
                //set a renderer for a specific column(index) 
                ->setRenderers(
                        array(
                            1 => array('view' => 'logs/renderers/log_renderer.html.twig'),
                            2 => array('view' => 'default/renderers/datetime.html.twig'),
                            3 => array('view' => 'logs/renderers/user.html.twig')
                        )
                )
                ->setOrder("lg.when", "desc")                                // it's also possible to set the default order
                ->setGlobalSearch(true);`

This is my log_renderer.html.twig for example:
{{ dt_obj.message }} ({{ dt_obj.module }}) <br />Standort: {{ dt_obj.hierarchyId }} <br />{{dt_obj.user }} {{ dt_obj.when|date('d.m.Y H:i') }}

I have a renderer for 3 columns. The only global search that works is for the first column, where is no renderer. Any suggestions?

You should set a datatable id in your action with "setDatatableId" using the id from your view

Hi,
I've an issue when implementing the datatable bundle.
I've followed the doc, set up the bundle with composer etc.

In my controller :

public function userInformationsAction(Request $request, $mode = null') {
        $this->initManagerAndSession();
        $user = $this->getUser();

        if ($mode == 'showInvoices') {
            return $this->render('NutriFitBundle:User:userInformations.html.twig', [
                'user' => $user
            ]);
        } else {
            throw $this->createNotFoundException();
        }
    }

/**
     * Create DataTable for invoices list
     * 
     * @return unknown
     */
    private function computeDataTable() {
        $dt = $this->get('datatable')
                    ->setEntity('NutriFitBundle:Facture', 'f')
                    ->setDatatableId('invoice')
                    ->setFields(
                            [
                                'Reference' => 'f.reference',
                                'Date' => 'f.creationDate',
                                'Amount' => 'f.amount',
                                'Shipping Data' => 'f.shippingDate',
                                'Payment Type' => 'f.paymentType',
                                "_identifier_"  => 'f.id'
                            ]
                    )
                    ->setWhere('f.user = :user',
                            ['user' => $this->getUser()]
                    )
                    ->setOrder('f.creationDate', 'desc');

        return $dt;
    }

    /**
     * Build datatable view for user invoices
     * 
     * @param Request $request
     */
    public function invoicesDataTableAction(Request $request) {
        return $this->computeDataTable()->execute();
    }

In my twig :

{{ datatable({
                    'js' : {
                        'ajax' : path('invoices_datatables')
                    }
                })
            }}

When I'm accessing to this view, I've the following error :
An exception has been thrown during the rendering of a template ("No instance found for datatable, you should set a datatable id in your action with "setDatatableId" using the id from your view")

When I'm trying to query the url action directly, I've a correct JSON :

{"draw":0,"recordsTotal":"1","recordsFiltered":"1","data":[["jrgoireg-21",{"date":"2012-01-01 00:00:00","timezone_type":3,"timezone":"Europe\/Berlin"},25.5,{"date":"2012-01-02 00:00:00","timezone_type":3,"timezone":"Europe\/Berlin"},"CB",1]]}`

Could you help me for doing it work please ?

Thanks by advance,

Regards,

Display a Datetime Value

I have a problem with displaying a datetime Object. I have a field which is declared as "datetime" in my entity. When I put this field in the "setFields" Value ("StartDtTm" => 'csm.startdttm') I get this in my table:
[object Object]
When I look at the json return it is an array:
{"date":"2015-12-05 02:01:54","timezone_type":3,"timezone":"Europe/Berlin"}

But I have no idea how to get the "date" value from this array. 'csm.startdttm.date' is not working.
Please help me

Inject the ManagerRegistry instead of the EntityManager

Like said in the post wrote by Matthias Noback, Inject the ManagerRegistry instead of the EntityManager

http://php-and-symfony.matthiasnoback.nl/2014/05/inject-the-manager-registry-instead-of-the-entity-manager/

Alias detection does not respect "AS"

Took me an hour to find out, why simple field

$x->setFields([
 'Entities' => 'SIZE(x.entities)'
]);

Throws an error.

Notice: Undefined index: SIZE(x_entities)

According to docs, it needs to be aliased, so I tried

$x->setFields([
 'Entities' => 'SIZE(x.entities) AS entities'
]); 

to receive:

Notice: Undefined index: SIZE(x_entities) AS entities

Had to read the code to find out that this is not DQL, it is case-sensitive and to work - aliasing keyword has to be written lowercase:

$x->setFields([
 'Entities' => 'SIZE(x.entities) as entities'
]); 

I believe that aliasing should be respected case-insensitive. Or, at least, there should be a bold statement in the docs 😉

Setting custom entityManger

I had to switch from AliDatabundle to another which is compatible with Symfony 3.

Unfortunatly, and don't understand why, customizing "entityManager" has not be forked here.. Why that ?

I have several entityManagers and, obviously, the one I have to use for my DataTable is not the one set by default.

Is this a missing ? Does it exist another way (in config.yml for example) to set which entityManger using ?

Twig Problem

Hi,
if i use this format

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.css"/>
<script type="text/javascript" src="//code.jquery.com/jquery-2.1.4.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.js"></script>

{{ datatable({
        'js' : {
            'ajax' : path('route_for_your_datatable_action')
        }
    })
}}
{{ datatable({
        'js' : {
            'ajax' : path('route_for_your_datatable_action')
        }
    })
}}

I don't see any

If i use this

% block body %}
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.css"/>
    {{ datatable_html({
            'id' : 'dta-offres'
        })
    }}

{% endblock %}

{% block javascripts %}
<script type="text/javascript" charset="utf8" src="//code.jquery.com/jquery-1.10.2.min.js"></script>
<script type="text/javascript" charset="utf8" src="//cdn.datatables.net/1.10.9/js/jquery.dataTables.js"></script>
{{ datatable_js({
        'id' : 'dta-offres',
        'js' : {
            'dom': '<"clearfix"lf>rtip',
            'ajax': path('route_for_your_datatable_action'),
        }
    })
}}
{% endblock javascripts %}

I see the results but i have the reinitialise datatable error
DataTables warning: table id=dta-offres - Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3

Second question
how can I have a rendering as in your example image?

Refresh Table Data After Multiple Select

When following the 'Multiple Actions how do I add a checkbox for each row?' example I experienced an issue where the datatable was not refreshing after the record had been updated in database.

Seems that the success function in multipleRaw.js.twig was not working for me.

 success: function(msg) {
                    $('#{{ id }}').trigger('dt.draw');
                },

It was replaced with;

success: function(msg) {
                    $('#{{ id }}').DataTable().ajax.reload(null, false );
                },

which solved it for me (and refreshed the table keeping the current pagination.

How to access a method of the repository?

Example I need get record for

$res=$qb->getQuery()->getResult();
$email=$res[0]->getOrderCustomer()->getEmail();

but I do not know how to access the method from: setFields()
if I try this I get this error

Notice: Undefined index: o_getOrderCustomer_getEmail

`$datatable = $this->get('datatable')
->setFields(
array(

                "Id" => 'o.id',
                "Email" => 'o.getOrderCustomer.getId()',// error
                "_identifier_" => 'n.id')
        )->setGlobalSearch(true);`

Help Please

Twig 2.0?

Currently bundle locks on using twig ~1.8, is there any specific reason to stay at this version?

After aliasing 2.0 as 1.89 I didn't find any blockers - DatatableBundle seems to be working ok.

Embeddable field problem

Hello,
When using embeddables the getData fucntion at Waldo\DatatableBundle\Util\Factory\Query\DoctrineBuilder doesn't work.

We think that this could be solved:

Embeddables: Fields thats comes like alias.address.zipcode then the 331 line not work.

We suggest replace the line 331 inside the get_scalar_key function:

$_f = str_replace('.', '_', $_f);

by:

$pos = strpos($_f,'.');
if ($pos !== false) {
    $_f = substr_replace($_f,'_',$pos,strlen('.'));
}

What's do you oppinnion about it?

Thanks

Inject Parameters into Multiple Route

Is it be possible for params to be injected in setMultiple so that the route can be built using them in twig?

ExampleController.php
$dt->setMultiple(
array(
'delete' => array(
'title' => 'delete',
'route' => 'delete',
'params' => array('id' => $id),
),
)
)

multipleRaw.js.twig
{% for key,item in multiple %}
<option value="{{ path(item.route), item.params }}">{{ item.title }}</option>
{% endfor %}

Records are not formatted

This is how my records came out

{"draw":0,"recordsTotal":"1","recordsFiltered":"1","data":[["Hillary","Test",1]]}

What is causing this problem?

No results with eg code

Hi,

Now it's works with symfony3, I get no results with my test.

the json is valid :

{"draw":1,"recordsTotal":"0","recordsFiltered":"0","data":[]}

If you have an idea why I get no results ;-)

thanks.

twig file is

<!-- include the assets -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.css"/>
<script type="text/javascript" src="//code.jquery.com/jquery-2.1.4.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.js"></script>

{{ datatable({
        'js' : {
            'ajax' : path('datatable')
        }
    })
}}

My Controller:

class DatatableController extends Controller
{
    /**
     * set datatable configs
     * @return \Waldo\DatatableBundle\Util\Datatable
     */
    private function datatable()
    {
        return $this->get('datatable')
            ->setEntity("MyBundle:Task\Task", "x")// replace "XXXMyBundle:Entity" by your entity
            ->setDatatableId('dta-offres')
            ->setFields(
                array(
                    "name" => 'x.name',                        // Declaration for fields:
                    "identifiant" => 'x.identifiant',                    //      "label" => "alias.field_attribute_for_dql"
                    "_identifier_" => 'x.id')                          // you have to put the identifier field without label. Do not replace the "_identifier_"
            )
            ->setWhere(                                                     // set your dql where statement
                'x.error = :error',
                array('error' => 0)
            )
            ->setOrder("x.dateCreated", "desc");                                // it's also possible to set the default order
    }


    /**
     * Grid action
     * @Route("/ajax-datable-reports", name="datatable")
     * @return Response
     */
    public function gridAction()
    {
        return $this->datatable()->execute();                                      // call the "execute" method in your grid action
    }

    /**
     * Lists all entities.
     * @Route("/list", name="datatable_list")
     * @return Response
     */
    public function indexAction()
    {
        $respository = $this->getDoctrine()->getRepository('MyBundle:Task\Task');
        $r = $respository->findAll();
        // this works correctly and I get 92 results


        $this->datatable();                                                   // call the datatable config initializer
        return $this->render('MyBundle:layouts/rapports:datatable.html.twig');                 // replace "XXXMyBundle:Module:index.html.twig" by yours
    }
}

My Entity

/**

namespace MyBundle\Entity\Task;

 * @ORM\Entity
 * @ORM\Table(name="task")
 * @ORM\Entity(repositoryClass="MyBundle\Repository\TaskRepository")
 */
class Task
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(type="string")
     */
    private $name;

    /**
     * @ORM\Column(type="boolean")
     */
    private $error;

    /**
     * @ORM\Column(type="string")
     */
    private $identifiant;

    /**
     * @ORM\Column(type="string")
     */
    private $data;

    /**
     * @ORM\Column(type="string")
     */
    private $message;

    /**
     * @ORM\Column(type="datetime")
     */
    private $dateCreated;

    public function __construct()
    {
        $this->dateCreated = new \DateTime();
    }

    /**
     * @return mixed
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @param mixed $id
     */
    public function setId($id)
    {
        $this->id = $id;
    }

    /**
     * @return mixed
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * @param mixed $name
     */
    public function setName($name)
    {
        $this->name = $name;
    }

    /**
     * @return mixed
     */
    public function getError()
    {
        return $this->error;
    }

    /**
     * @param mixed $error
     */
    public function setError($error)
    {
        $this->error = $error;
    }

    /**
     * @return mixed
     */
    public function getIdentifiant()
    {
        return $this->identifiant;
    }

    /**
     * @param mixed $identifiant
     */
    public function setIdentifiant($identifiant)
    {
        $this->identifiant = $identifiant;
    }

    /**
     * @return mixed
     */
    public function getData()
    {
        return $this->data;
    }

    /**
     * @param $data
     * @return $this
     */
    public function setData($data)
    {
        $this->data = $data;
        return $this;
    }

    /**
     * @return mixed
     */
    public function getMessage()
    {
        return $this->message;
    }

    /**
     * @param mixed $message
     */
    public function setMessage($message)
    {
        $this->message = $message;
    }

    /**
     * @return \DateTime
     */
    public function getDateCreated()
    {
        return $this->dateCreated;
    }

    /**
     * @param $dateCreated
     * @return $this
     */
    public function setDateCreated($dateCreated)
    {
        $this->dateCreated = $dateCreated;
        return $this;
    }
}

Don't Use The Package! - Paketi Kullanmayın Sakın!

Arkadaş öyle lanet bir şey ki paketi kullandıktan sonra hayata 1-0 (👎) geriden başlıyorsun. Sizlere tavsiyem symfony ile datatables ihtiyacınızı bundle kullanmadan sağlamaktır.

Datatables configuration not working

Datatables js configuration are not working in config.yml

Here is my configuration:

# Ajax Datatables configuration
waldo_datatable:
    all:
        search:           false
    js:
        pageLength: "10"
        lengthMenu: [[5,10, 25, 50, -1], [5,10, 25, 50, 'All']]
        dom: '<"clearfix"lf>rtip'
        jQueryUI: "false"
        bFilter: "false"

I am unable to see any JS and datatables changes as per those configurations

The changes are also not reflected by specifying them on twig options as well

{{ datatable({
    'js' : {
        'ajax' : path('datatable'),
        'bFilter':false
    }
})
}}

any work around on this issue please ?

More information about how to add actions on each row

Hello @waldo2188 can you give more information about how to add some actions in a ceil for each row?

Using the solution in the README is not really working for me, maybe I'm missing something...

Using version 4.0.2 with dataTable version 1.10.12 from their CDN.

Datetime field

It is possible to set a datetime in column with a specific format? Something like:

$this->get('datatable')
            ->setEntity("AppBundle:EmailEntity, "e")
            ->setFields(
                array(
                    'Created On' => 'date_format(e.createdOn, "%M")',
                    "_identifier_" => 'e.id',
                )
            )

Dutch translations inside DE File

Dutch translations have been put inside the German (messages.de.yml) file.

Variable "not_filterable_fields" does not exist.

Hi,
I've an issue when implementing the datatable bundle.
I've followed the doc, set up the bundle with composer etc.

In my controller :
/**

• set datatable configs
• @return \Waldo\DatatableBundle\Util\Datatable
  */
  private function datatable()
  {
  return $this->get('datatable')
  ->setDatatableId('produits')
  ->setEntity("AppBundle:Produit", "x")
  ->setFields(
  array(
  "Name" => 'x.name',
  "Description" => 'x.description',
  "price" => 'x.price' ,
  "quantity" =>'x.quantity',
  "identifier" => 'x.id'
  )
  )
  ->setHasAction(true);
  }

/**

• Grid action
  *@route("/grid", name="produit_grid")
  */
  public function gridAction()
  {
  return $this->datatable()->execute();
  }

  /**

  • Lists all produit entities.
  • @route("/", name="produit_index")
  • @method("GET")
    */
    public function indexAction()
    {
    $this->datatable();
    return $this->render('AppBundle:produit:index.html.twig');
    }

In my view:

<script type="text/javascript" src="//code.jquery.com/jquery-2.1.4.min.js"></script> <script type="text/javascript" src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.js"></script>

{{ datatable({
'id' :'produits',
'edit_route' : 'produit_edit',
'delete_route' : 'produit_delete',
'js' : {
'sAjaxSource' : path('produit_grid')
}
})
}}

the result of the grid action is fine:
{"sEcho":0,"iTotalRecords":"2","iTotalDisplayRecords":"2","aaData":[["pc","ordinateur portable",15,25,1],["ss","sqs",45,45,2]]}

but when i try to show my product's list page this error appears:

Variable "not_filterable_fields" does not exist.

Please any help? thank u

Symfnoy3 - compatible ?

hello, can we using bundle with symfony3 👍
because after installing bundle i have this error :

Unable to parse file "/var/www/html/Symfony/vendor/waldo/datatable-bundle/DependencyInjection/../Resources/config/services.xml".

how fix it please ?

Problem with js defaults for number of entries

At least in DatatableBundle version 4.0.1 the "show number of entries combo" is generated wrong in the final html, it looks like:

<select class="">
<option value="[">[</option>
<option value="[">[</option>
<option value="1">1</option>
<option value="0">0</option>
<option value=",">,</option>
<option value=" "> </option>
. . . .

Could be fixed by changing the line

"lengthMenu" => '[[10, 25, 50, -1], [10, 25, 50, "All"]]',

by

"lengthMenu" => array(array(10, 25, 50, -1), array(10, 25, 50, "All")),

in vendor/waldo/datatable-bundle/DependencyInjection/WaldoDatatableExtension.php

I've made a mostly default install, jquery is 1.10.2, DataTables.min 1.10.9.

Thanks for the bundle - at least my first steps were rather easy!

Matthias