A few troubles with a collection field

[wtfred]

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

[ChadSikorra]

Yeah, there probably isn't a very good reason not to allow an assoctiation to be directly referenced as a mData column as opposed to strictly allowing only fields. I suppose it comes down to what the default behavior should be when it happens.

In the case of a single entity association, it's easy enough to assume to just pull all data for the association. In the case of a many-to-many or one-to-many relation, I would think an array of objects such as...

"orders":[{"id":1, "firstName":"foo","lastName":"bar"},{"id":2,"firstName":"foo","lastName":"bar" }]

...would be expected? At least that seems like it would be the easiest to manipulate. Or what would be the expected format of the JSON data to make the string notation "mRender": "[, ].id" work? I looked at some examples on the datatables website but didn't see anything that specific. I'll try to look at this some more over the weekend.

[ChadSikorra]

Ok, finally have some time this weekend :) I was able to reproduce The iTotalDisplayRecords issue. That should be fixed with 2ec719e. Still have to work on the other issue of allowing it to return associations as well as fields.

[ChadSikorra]

I take that back, that commit doesn't completely fix the issue. It fixes it in quite a few cases, but it appears that there is something else I'm not accounting for. When I run certain global searches on the datatable the iTotalDisplayRecords will sometimes be incorrect when there are entity associations involved.

[ChadSikorra]

Okay...that commit actually does fix iTotalDisplayRecords. However, the larger issue is that when a collection is involved the Doctrine QueryBuilder does not behave correctly with limits and offsets. Apparently it even says this in their documentation...

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#first-and-max-result-items-dql-query-only

So the issue I noticed is that when DataTables.js requests 10 results, the Datatables class for this bundle would actually return only a portion of the results depending the collection size. Luckily there is a Paginator class available since Doctrine 2.2 that solves this issue...

http://docs.doctrine-project.org/en/latest/tutorials/pagination.html

So the following commit implements the Paginator which should fix the strange counting issues once and for all
08f5972. At least it does for me anyway, and it doesn't cause any other issues I can find with other scenarios.

[ChadSikorra]

I made another commit that should resolve the issue where you try to render orders.id but are forced to access it from the full object in datatables.js. The data object should no longer be empty so you can loop on it to do what you need, or just let it print out in comma delimited form. See 63ba807

It still does not support the "mRender": "[, ].id" notation. To my understanding of the notation, that is what I would expect if the mData was referencing an association directly. I'd like to implement referencing an association in the mData, but it seems to be a lot less trivial than I originally thought. The bulk of the issues occur with collections or collections inside of collections, and parsing it out correctly so it can be accessed normally from datatables.js.

[wtfred]

Hi ! I finally have time to test your updates :)
So :
--> Lets say i have a product who can be bought by several customers, i want to list all my products and details about customers.

"mData": "customers.name",
"sDefaultContent": "",
"mRender": "[, ]"

This works almost perfectly fine. If i have multiple customers, i got "customer1, customer2, customer3" but if i have only one customer for the product, i got "c, u, s, t, o, m, e, r, 1"
Dunno if is a little bug from the bundle or datatables.js, but it can be fixed it by doing this :

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

As u said, The data object is no longer be empty, and the iTotalDisplayRecords works fine too, thanks !