DataTablesSrc
DataTablesSrc copied to clipboard
DataTables
Support column ids in order
Adds support for sorting on column identifiers, as discussed in https://github.com/DataTables/DataTables/issues/837
Example usage:
$(document).ready( function() {
$('#example').dataTable( {
orderByName:true,
order:[["userId", "desc"]],
columns:[
{
name:"userId",
}
]
});
});
Alternatively, you can key off of column.data (if it's a string), or a <th> element's id attribute.
<table id="example">
<thead>
<tr>
<th id="userId">User Id</th>
<th id="date">Date</th>
</tr>
</thead>
</table>
<script>
$(document).ready( function() {
$('#example').dataTable( {
serverSide:true,
ajax:"/path/to/endpoint",
orderByName:true,
order:[["userId", "desc"]],
});
});
</script>
Changes:
- added new
orderByNameconfiguration option,falseby default. Iffalse, everything works the way it used to. Iftrue, conversions between column identifier and column index will happen as necessary. - added two new functions:
_fnColumnIndexToColumnIdand_fnColumnIdToColumnIndex - changed the default for
aaSortingto empty array (because I can't convert the "0" that used to be there until after we haveoSettingsinitialized), but I initialize it to the old defaults later in the process - fixed some PHP warnings (I'm on 7.0, and it doesn't like class-name-as-constructor-name. The changes I made are compatible with 5.4)
- removed some unused variables
I tested tables from markup, from AJAX, with\without columns, with\without columns.orderData, with\without order and so on. Did I miss anything?
Also, you mentioned something about this possibly conflicting with extensions. If you point me at the ones you had in mind I can investigate.
Super - thank you!
As we discussed before I'm not going to pull this in immediately, but I will use this as the basis of adding support for this into DataTables in future. One thing - I don't see any need for orderByName. Can it not just detect if there is a string or a number and then get the index as required?
I'll bring in the PHP changes though! Unfortunately I don't known how (if!) you can cherry pick part of a merge in github, so I'll either do those changes manually, or pull in another PR.
I don't see any need for orderByName. Can it not just detect if there is a string or a number and then get the index as required
I started off with that assumption as well, but ran into trouble. Converting from a logical column id to a physical index is no problem: you just check for string before converting. Going the other way is trickier though, because right now I check name, data, and row id, and a user of this app might have any number of those things but not be prepared for this new feature, so compatibility would break for him in a server-side scenario.
One idea would be to modify the data.order attribute that gets sent to the back end. Right now we send this:
{
column: 1,
dir: "asc"
}
But I would be perfectly happy with this:
{
column: 1,
type:"userId",
dir: "asc"
}
Users not interested in the logical ids simply ignore the new field, folks like me modify our ajax method (or server-side code) to expect type. If you're cool with it I'll go ahead and update the PR.
But that's just one thought. Maybe you can suggest a different approach?
Oh, and a question. I'd like to go ahead and start using my modified code in my production environment, but the DataTablesSrc repo doesn't actually contain any built code, so I can't just point my bower.json at the forked repo. I'm not clear on how this repo co-exists with the other one. Do I fork both and point the one at the other? Do I do something ugly, like copy-pasting a built version into my app's javascript directory? Apologies if this is spelled-out somewhere and I missed it...
I went ahead and made the changes. I couldn't resist--it really is nicer without the orderByName thing, and it feels more automatic and natural. You can even use a mix of physical indices and logical ids; it just works.
Sorry I had thought this was entirely about what the order option and order() API method would accept, but from your above message it also relates to what is sent to the server-side.
Breaking it down - my idea was that order and order() should accept either a column index or a column name (defined by the columns.name parameter in the table initialisation). You'd implemented that in the above. I need to look through in detail what effect this will have on the plug-ins. Ideally the array format that is currently used should be updated to be objects which would make this sort of thing so much easier!
The second part, the data being sent to the server I see as a separate issue. Currently the ordering information sent to the server contains dir and column parameters - you want to add the column name (if I've understood correctly) - again that which is defined by columns.name.
This is already indirectly possible since the server-side processing request contains the column names in it - but your server-side script would need to look the name up from the index. I can see that including it in the sorting array might be useful, but it is slightly redundant since the name information is already being sent as part of the columns array.
I've merged master and squashed my commits.
Ideally the array format that is currently used should be updated to be objects which would make this sort of thing so much easier!
Yes, the first thing I usually wind up doing when I use DataTables professionally is write a wrapper around it with its own API for columns and order, looking something like this:
$("#myTable").DataTableWrapper({
columns:{
userId:{
visible:false
},
date:{
visible:true
}
},
order:[["userId", "asc"]]
});
// Note: technically objects are unordered in Javascript. Modern javascript implementations do
// respect the order--as a courtesy--but there are no guarantees with older ones. So I don't
// advise baking this into Datatables until you're ready to stop supporting old browsers.
Inside my wrapper I have a lot of utilities for converting my columns to one of your columns, converting data.order to a format my server understands, and so on. I'm now incorporating DataTables into my own site, and I thought it would be nice to see if we could diminish the need for me (and others) to repeat some of that work.
Breaking it down - my idea was that order and order()
~~Ah, drat, I forgot to check if the API methods still work. Will confirm after this post.~~ works fine
might be useful, but it is slightly redundant since the name information is already being sent as part of the columns array
Definitely useful. As a (long) aside, one thing I've noticed about the samples and documentation around Datatables is a bias towards the assumption that one will be writing server code to support Datatables, but I have never been in that situation. In my experiences the back end has always been firmly in place and with locked-down API schema validation that doesn't allow all the stuff Datatables wants to send in its default, basic form. So in my wrapper I always have to pipe things through the ajax config option, and I always have to teach my wrapper how to convert those order columns to types. I've never been able to find any samples in the documentation that show how to do this (specifically, digging into data for order and pagination info in an ajax callback), so I have to reinvent it every time. Most recently, I started out doing it all again for my own code and stupidly wasted a lot of time digging around in the oSettings argument passed to the ajax callback in the debugger before I realized everything I needed was in data (I guess I should have made the mental connection to jQuery's 'data' arg it passes to its own ajax).
Long story short: "might be useful" is an understatement!
So you're right. Everything I'm talking about is already possible with all the information DataTables is passing around. My goal is to make DataTables easier to drop in and use for people with use cases similar to mine.
I need to look through in detail what effect this will have on the plug-ins.
Is that something I can help with?
one thing I've noticed about the samples and documentation around Datatables is a bias towards the assumption that one will be writing server code to support Datatables
To some extent that is true. But it really boils down to the fact that the data needs to be submitted in some form, and there is no universal standard for this sort of thing so I picked something that works well for the features DataTables provides. You can use the ajax.data option in DataTables to manipulate the data being submitted to an API - almost every API is going to be a little different with the parameters it accepts so this was the best compromise I could offer.
In the case of the name for the sort you are still assuming that the API is going to know to look in order[ i ][ name ] to get the column name - which might be true of the API you are using but certainly isn't going to be universal.
Since the name information is already being submitted for each column, looking up a name from the column index in the ordering array is really easy. I'm reluctant to add redundant information since that just increases the payload size, and as I say, it doesn't really resolve an issue with a generic API.
Note: technically objects are unordered in Javascript.
Its really nice the method you show above, but the issue you state is the reason that isn't likely to appear in DataTables anytime soon. Even not considering older browsers, the "as a courtesy" doesn't feel strong enough for me to ship this baked into DataTables.
Is that something I can help with?
Thanks for the offer! We are currently reimplementing unit tests for DataTables core and the extensions. This is the kind of feature change where any errors should immediately be picked up by the tests. It isn't likely to be included until that work is completed I'm afraid. It is however a goal for the next major version of DataTables.
In the case of the name for the sort you are still assuming that the API is going to know to look in order[ i ][ name ] to get the column name - which might be true of the API you are using but certainly isn't going to be universal.
Not at all. Again, I'm not in a position to let DataTables talk directly to my back end. By including type in data.order I am simply removing the need to do the work of converting physical column indices to logical ids in my ajax method. Here's what my ajax method currently looks like:
var dt = $('#action-log').DataTable({
ajax:function(data, callback, settings) {
util.ajax("/api/Actions/find", {
type:"GET",
data:{
pagination:{
offset:data.start,
limit:data.length
},
// I only have to do the map operation here because if
// I sent the order array as-is I would get a validation
// error for the unexpected 'column' property
sort:data.order.map(function(sort, idx, arr){
return {
type: sort.type,
dir: sort.dir
};
})
},
dataType: "json",
success: function(response){
callback({
data:response.items,
recordsTotal:response.pagination.totalItems,
recordsFiltered:response.pagination.totalItems
});
}
});
},
columns:[
{
title:"Date",
data:"date"
}, {
title:"User",
data:"userId"
}, {
data:"type",
title:"Type"
}, {
data:"description",
title:"Description"
}
],
pageLength:25,
rowId:"actionId",
searching: false,
serverSide: true,
});
});
So people who are using an ajax method like me will have it a little easier, and people who are writing a bespoke back end are already prepared to consume what DataTables is sending (and this will relieve them of the need to convert, as well).
the "as a courtesy" doesn't feel strong enough for me to ship this baked into DataTables
Preaching to the choir. I did advise against it! I did it this way at my last company because we were writing in-house tools and everybody was on FF or Chrome, and it made all the conversions I had to do more straightforward. Just thought I'd show you in case you have some idea percolating to release a new major version someday that officially drops support for older browsers (like jQuery did not too long ago). I'm not hung up on it.
I'm reluctant to add redundant information since that just increases the payload size
Do you know how many people let DataTables talk directly to the back end? It's just not something I can see happening on a serious, professional scale, and I suspect it's something done by small, mom-and-pop grade projects. if you're serious about APIs, you're going to want to validate the input, and I can't see anybody wanting to take on the work of validating all the information that DataTables wants to send along. You would have to write a really long, complex schema, and its documentation would essentially just mirror DataTables's documentation. If I tried to pitch that to the back end team at my current company i would get a lecture about how important it is for the back end to not be bound to any specific front end, etc, etc.
In other words, to me--and I expect a lot of others--the data is already much too long and complex to be sending to the back end. Adding a friendly type property just makes ajax easier to write for those of us who are already doing it, and doesn't affect the people who aren't already turned-off by the enormity of data.
Or look at it this way: if type was sent along, and DataTables was talking directly to the back end, I wouldn't need columns in my payload at all, and the payload would drop in size dramatically!
I can tell that my arguments aren't getting me anywhere. How about a different approach: what about exposing an API method for doing the conversion? Or could an extension do all this?
Meanwhile, I'm eager to start using my own changes, but I'm still not clear on how one consumes one's own fork without doing a build step. Any advice?
Oh, and a question: would purchasing paid support serve to accelerate any of this? I see you have some PRs sitting around from more than a year ago, and I don't want to meet the same fate.
In other words, to me--and I expect a lot of others--the data is already much too long and complex to be sending to the back end.
Yup. And for that reason adding redundant information isn't something that I particularly want to do. It would just make the situation worse.
can tell that my arguments aren't getting me anywhere. How about a different approach: what about exposing an API method for doing the conversion? Or could an extension do all this?
You can already do this with the preXhr event - use that to modify the data as you require. The preXhr event bubbles so you should be able to do $('body').on('preXhr.dt', function ... ) to allow it to work with all tables from a single piece of code.
Meanwhile, I'm eager to start using my own changes, but I'm still not clear on how one consumes one's own fork without doing a build step. Any advice?
You have to build it I'm afraid. There reason for the source repo and the multiple distribution repos is that the src repo contains information for multiple distribution repos - specifically the styling options for Bootstrap, Foundation, etc. Having them in individual distribution packages makes them easily consumable by npm (etc) which isn't so easy if a single repo provided all of them.
would purchasing paid support serve to accelerate any of this?
In this case, to be honest no, sorry. I'd like the tests to be completed before any major changes are made in DataTables (this isn't a major change in fairness, but it will result in a few areas being touched). Its my fault that the tests slipped in the first place - that shouldn't have been allowed - sorry. Tom, who is working on the tests this summer, but otherwise it is just me, which is why it isn't always possible to pull into all PRs in a timely manner as I need to try and balance the free open source aspect with being able to build the business. Paid support for a PR is not more likely to make me pull it in (although obviously I would be able to spend more time considering the impacts).
Yup. And for that reason adding redundant information isn't something that I particularly want to do. It would just make the situation worse.
You missed this point:
Or look at it this way: if type was sent along, and DataTables was talking directly to the back end, I wouldn't need columns in my payload at all, and the payload would drop in size dramatically!
You could make the payload significantly smaller and easier to work with! I realize that would break compatibility for people already expecting the full columns payload, but again, I have to wonder how many people there are out there that are actually doing all this on the back end.
Again, It's already too long and complex, and always has been. When you add one more piece of information to something that is already too long, it is still too long. Nothing has changed. Adding one more small piece makes no difference to the folks who are already using ajax (or preXhr ), and the folks who don't care about a big, complex payload, well, they don't care. If columns wasn't the last straw for them, then there's no way in hell this would be.
You can already do this with the preXhr event - use that to modify the data as you require.
I feel like my central point is being lost. I realize I can add code to do this. I can do this in my ajax method. I can do it with preXhr. The point is I'm philosophically tired of doing the work. I don't want to have to write a wrapper anymore every single time I want to introduce your wonderful app to a new environment that has its own ideas about how APIs should be laid out. I feel that since DataTables already has most of the understanding of mapping column ids to physical indices (via 'data' and 'name'), it seems only natural to close the loop and expose it to data.order.
Having them in individual distribution packages makes them easily consumable by npm (etc) which isn't so easy if a single repo provided all of them.
I'll have to take your word for that; I don't think I know of any other projects that have a similar multi-repo sensibility--they all just sort of include everything in the one repo, and you simply include only the scripts you need in your page (eg. https://github.com/browserstate/history.js/tree/master/scripts/uncompressed) . Your tendency to not accept PRs wouldn't be so bad if the source was usable as a fork, but the build step we're required to do isn't practical because of its somewhat DIY design and all the exotic dependencies (PHP, closure compiler). It took me at least a full day just to get DataTables to build, and it's not reasonable for me to expect a contributor to my own project to do the same thing just to get up and running. Have you thought about modernizing some of it, via grunt? I think at this point every front end person not living under a rock these days has come to expect it as part of the build flow, and there's nothing in your shell scripts or PHP code that you couldn't do with a simple npm script. There's even an existing closure compiler grunt task (though I have to wonder if closure compiler is giving you any gains to speak of over uglify when you're not using the advanced compiling setting). I might have to consider doing all that in my fork. Good lord, the things I'll do to get a change made...
You could make the payload significantly smaller and easier to work with! I
I'd appreciate some insight on that. The search regex option and column orderable parameters are the ones I am considering dropping in future.
I feel like my central point is being lost. I realize I can add code to do this. I can do this in my ajax method. [...]
What I don't get is how changing the data format submitted will resolve this. It will just change it to be suitable for your API, but not for someone else's (for example). I don't know how to make it nice and generic unless you have any suggestions.
Your tendency to not accept PRs wouldn't be so bad if the source was usable as a fork
I'm not sure that I tend not to accept them. I'm just careful. I feel I accept the majority.
Have you thought about modernizing some of it, via grunt?
Very much so. Its on the cards.
though I have to wonder if closure compiler is giving you any gains to speak of over uglify when you're not using the advanced compiling setting
Its small, but yes, in the case of DataTables I do notice a small difference.
Good lord, the things I'll do to get a change made...
I haven't rejected the change to the data to be submitted for server-side processing. I've said I'll probably include it in the next major version. I don't want to include it right now because I want to test it properly. I appreciate your input hugely, but I'm afraid I can't just immediately pull it in because I messed up long term with the tests.
I have to get to work and will reply later, but I just wanted to say I appreciate your time and diligence, and I'm enjoying the discussion. More later.
Starting over. Here's what I'm proposing: Add type to what gets sent in data.order. So instead of this:
http://take.ms/QaUnV
you get this:
http://take.ms/DP5lZo
Why? Simply so that I don't have to perform the act of converting the physical column index in data.order (eg. "0") to a column id eg ("date") myself. DataTables already has the concept of mapping column ids (via name and data) to indices, so why wouldn't it perform such a crucial and important courtesy? After all, the conversion must happen, right? I don't know of any databases that know how to sort on a numerical column index; they all want a name of some kind.
You argue that data is too long, and that the addition of type only makes it worse. My response is that to people like me with preexisting APIs truly separate from the concerns of the front end, data is already too long and always has been. And to be clear, when I say it's "too long", I really mean that it's too convoluted and specialized to make it past my APIs restrictive validation. Any format that Datatables wants to send directly would be, full stop. That's going to be true for anyone who didn't write a back end around DataTables. The physical length is of no concern. We're talking about a few hundred bytes on the outgoing request, total.
So, by my count there there are two kinds of people using DataTables with server-side functionality:
-
The Heavy Users. People that have a preexisting API on the back end (including me, and anyone trying to introduce DataTables to an environment where the APIs already exist, are locked-down, or follow strict design patterns). They are properly separated from the concerns of the front end, and are likely being consumed by a variety of front end apps. As far as the people in this camp are concerned the
dataargument may as well be 30MB in size because we don't and can't directly use it, even if it were tiny. We have to useconfig.ajax(or possiblypreXhr), pluck only the information out of thedataargument that we need (http://take.ms/BlRKSg), transform it into the shape our API expects, and forward it along (and likely transform the response as well, but that's no big deal). -
The Casual Users. People that wrote a back end around DataTables. The students. The hobbyists. The Mom and Pops that hired someone from Freelancer.com. They are not using
config.ajax. Their APIs are almost certainly not validating input. If the size and complexity ofdatawas not already a deal breaker for them, there's no way my extra 8-15 bytes would be.
(I'll allow a theoretical third camp of users that build a back end API around DataTables AND are doing full validation on everything in data, AND reject any incoming input that has extra arguments (ie. "type"), I would wager my PS4 that the number of people in this camp can be counted on one hand, and to them I apologize for order[].type)
Now, let's say that the physical size--and not complexity--of data really is a concern to you or the people in the Casual Users camp. You don't want to include type because you think it will increase the size and be redundant. My answer is that columns is the redundant information, and could be dropped. Once we have order[].type, is there any need to go poking around in columns for anything? I'm looking at what's in there right now: I see 'data' and 'name', and neither of those are necessary once you have order[].type. I see orderable, which seems unnecessary as well; the API makes its ordering decisions based on data.order. searchable and search seems pointless as well, you already have data.search to examine. I suppose you could argue that an API might need data.columns to decide which fields to return, but I suspect that the people in the Casual Users camp are not using back end APIs that sophisticated, and if they are, they could always defect to the Heavy Users camp and start using config.ajax. Or perhaps data.columns could devolve to a simple array of column names.
I'm playing devil's advocate, here--I don't really expect you to just drop columns--I'm trying to illustrate that it's a bit disingenuous to be fighting off a small but profoundly useful (again, everybody has to convert at some point) addition when the relatively burdensome columns has already contributed so heavily to the complexity in data.
Yup - as I mentioned above I will likely include the column name in the submitted data in the next version. Why I'm not pulling it in at this very moment:
- The change can effect both "heavy" and "casual" users (in fairness I know many casual users who could code rings around "professionals") since, by default DataTables sends a GET request. Many HTTP servers have a limit on the request string and this will enviably be enough to push some from working to not working (a small number yes)
- The
columns.nameparameter is very rarely used - perhaps it needs to becolumns.datathat is submitted, but that can be an object or function... This is part of my point about creep. As we've both noted the request is already sending a fair amount of information - this is just more and its redundant since it is possible to get it other ways. - I want to include this in unit tests which are currently being worked on
- At the moment all you need to do is
columns[ order[0].column ].nameto get the name. Yes, I realise this isn't going to fit with all APIs.
I'm not dismissing it. I'm sorry if you feel I am. The fact that I've not closed it and have spent time replying will hopefully say otherwise.
Yup - as I mentioned above I will likely include the column name in the submitted data in the next version.
Good enough. That's all I needed to hear, thanks.
Many HTTP servers have a limit on the request string and this will enviably be enough to push some from working to not working (a small number yes)
Just curious, can you name an example of a server technology that has a limit in the neighborhood of the hundreds or characters we're talking about? A few specific older browsers have fairly low limits (IE 8 and 9 have a limit of around 2k characters). All the rest have limits of 100KB or higher, and the official HTTP spec puts no bounds on it whatsoever. I don't think you're anywhere near any of these limits.
The columns.name parameter is very rarely used - perhaps it needs to be columns.data
Yes, I've been wondering if those two ideas could be merged.
Anyway, glad we've come to some sort of agreement. I'll find someone else to annoy, now. Thanks again for the careful consideration.
Apache's default is 8190 characters. With a lot of columns that can be hit fairly easily. Its not the browser that is the problem, but the defaults for the server.
Really I should have made the default for DataTables POST rather than GET. That was a mistake all those years ago... Will probably correct it for v2.
Interesting. That would have to be some table!
Really I should have made the default for DataTables POST rather than GET
Making POST an option is great (to get around the possible limitation you describe), but I don't know if I would advise it as a default. It's very trendy these days to be concerned about the semantics of our HTTP verbs; it would certainly run afoul of anybody with a REST design (because POST would be reserved for writes).
That is an excellent point thank you. A good enough reason to keep it as GET in fact.