Using $expand values in a datatable column.

[craigcsruk]

I had a few issues trying to use $expand values because of oData needing 'User/UserName' and datatables requiring 'User.Username'. After hacking around a bit I managed to get it supported.

Example for replicating the issue: Url: odata/Clients?$expand=User&$select=Id,Name,User/UserName

Javascript: $(document).ready((function () { $('#clientsTable').dataTable({

            "sPaginationType": "full_numbers",
            "aLengthMenu": [[50, 100, 200], ["50", "100", "200"]],
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": "/odata/Clients?$expand=User",
            "aoColumns": [
                            { "mData": "Id", "oData": "Id" },
                            { "mData": "FirstName", "oData": "FirstName" },
                            { "mData": "User/UserName" }
            ],
            "fnServerData": fnServerOData,
            "iODataVersion": 3,
            "bUseODataViaJSONP": false // set to true if using cross-domain requests
        });
    }));

The above implementation will perform a valid oData request but will cause an datatables warning "DataTables warning: table id=clientsTable - Requested unknown parameter 'User/UserName' for row 0".

Possible Fix To fix the issue for myself I changed aoColumns to allow an oData property and replaced all usages of mData with oData.

"aoColumns": [ { "mData": "Id", "oData": "Id" }, { "mData": "FirstName", "oData": "FirstName" }, { "mData": "User.UserName", "oData": "User/UserName" } ],

The fix worked for most features except the order by logic. Instead I implemented a hacky string replace:

var asOrderBy = []; for (var i = 0; i < oParams.iSortingCols; i++) { var columnName = oParams["mDataProp" + oParams["iSortCol" + i]].replace(".", "/"); asOrderBy.push(columnName + " " + (oParams["sSortDir_" + i] || "")); }

[mitaky]

Can we see the complete code for allowing odata property?

[craigcsruk]

I have added the full customisations I made to jquery.dataTables.odata.js here: http://pastebin.com/y0cHGeLQ

The implementation has grown somewhat since my first comment. The javascript now supports expanding as well as filtering on collections using the oData Any functionality.

At the bottom of the paste you can see my page implementation which should give you some idea on how to use it if your already familiar with using jQuery Datatables as well as the odata plugin.

Hope that helps.

[mitaky]

Thank you for that, Craig. I am struggling with expand for a day now. Good thing I found your issue here. I will give your implementation a try. Chreers

[worthy7]

@craigcsruk Just wondering if you could make a fork with your changes....

[craigcsruk]

Hi @Worthy7 as requested I have made a fork https://github.com/craigcsruk/jQuery.dataTables.oData with my changes. It hopefully includes the following improvements:

• Supports oData $expand
• Ability to select properties from sub objects by specifying aoColumns
• Ability to filter/search enum properities by specifying sType as enum
• Ability to search and filter collections
• Ability to specify if a column is search only (rather than filter)
• Included a filter/search delay so that you don't send requests too frequently
• Better int support

NOTE: Theses have only been proving for my use cases so they may or may not work for you use cases... but I am sure the improvement will at least help!

[worthy7]

Nice one, yours should become the master!

[madgecook552]

hy i am new for using filter with oData . i already can filter by oData base on this https://github.com/craigcsruk/jQuery.dataTables.oData. but can i also filter using render value?. for example: my mData is an ID, from the the ID i want to know date of birth with passing the ID to a function. in my case i only can filter by ID. but i wanna make filter by render value.