Sort table first by set text column order, then by numerical column

MJJ79

I'm relatively new to DataTables and need some assistance.

I have made a table that when first loaded needs to be sorted by a custom text order (col 1), and then by an ascending numerical order (col 12). Essentially, it's a football squad, ordered by position, then total points scored.

The text order is:

1 = 'GK'
2 = 'DF'
3 = 'MD'
4 = 'FD'
5 = '-'

Here is my code so far, which successfully orders col 1 alphabetically, then col 12 numerically;

new DataTable('#points-augsep', {
layout: {
topEnd: null
},
info: false,
ordering: true,
paging: false,
order: [[1, 'asc'], [12, 'desc']],
columnDefs: [
{ visible: true, targets: 0 },
{ orderable: false, targets: 5 },
{ orderable: false, targets: 6 },
{ orderable: false, targets: 7 },
{ orderable: false, targets: 8 },
{ orderable: false, targets: 9 },
{ orderable: false, targets: 10 },
{ orderable: false, targets: 11 },
]
});

rf1234

I'm relatively new to DataTables and need some assistance.

Here is my code so far, which successfully orders col 1 alphabetically, then col 12 numerically;

Sounds like you don't need assistance?!

Please use Markdown when posting code. Thanks.

MJJ79

Sorry, I'll use Markdown in the future. I can't see a way to go back and edit the post?

The issue is column 1 is ordered alphabetically, but I'd like it to be ordered in a custom format.

So by default, alphabetically it sorts:

DF
FD
GK
MD

I'd like help to sort it:

GK
DF
MD
FD

rf1234

I can't see a way to go back and edit the post?

"Regular people" like you and me can only edit a post within 15 minutes or so

I'd like help to sort it:
GK
DF
MD
FD

Sorry, I am probably a little slow in my head but what kind of sorting algorithm is this?

What you can do is to use a hidden column that has your sort order and sort it by that column, for example. That hidden column would need the following values for example ( number following => ).

GK    => 1
DF    => 2
MD    => 3
FD    => 4

"orderData" is probably also highly relevant for you:

Here I want to order the table by a column which is rendered (and therefore doesn't order properly) by a column that holds the original value which has the timestamp format and is therefore orderable. So whenever you click on the sorting arrows in the rendered column the table doesn't order by the rendered column but by the timestamp field.

columnDefs: [
        // targets may be classes
        {   targets: "scRenderedUpdateTime", orderData: $('#scUpdateTime').index() }
    ],

And of course using "sorting" and "ordering" adds to the confusion Not my language I have no idea why everyone uses English, lol. Too many synonyms in my opinion... Or as some coders would put it: Too "verbose"

https://datatables.net/reference/option/columns.orderData

allan

Have a look at this blog post where I introduced a plug-in that can be used to solve exactly this problem.

And of course using "sorting" and "ordering" adds to the confusion

Yes, sorry about that! I try to make sure I use "ordering" for the order of the table, while "sorting" is used for a data set (e.g. an array of data). See order() for applying an order to the table, and sort() to sort the data set.

Whether that is the right way to do it is up for debate, but that's how I do it

Allan

MJJ79

Thanks Allan, your High, Medium Low example is exactly what I need.

Please excuse my ignorance, but how/where do I add that to the script I already have (below)? Or is it a separate script?

new DataTable('#points-augsep', {
layout: {
topEnd: null
},
info: false,
ordering: true,
paging: false,
order: [[1, 'asc'], [12, 'desc']],
columnDefs: [
{ visible: true, targets: 0 },
{ orderable: false, targets: 5 },
{ orderable: false, targets: 6 },
{ orderable: false, targets: 7 },
{ orderable: false, targets: 8 },
{ orderable: false, targets: 9 },
{ orderable: false, targets: 10 },
{ orderable: false, targets: 11 },
]
});

kthorngren

1. You need to load the enum.js plugin. The CDN link is at the bottom of this blog section.
2. Define your mapping as described in this section.
3. The mapping is applied using DataTable.enum( [ ... ] ); as shown in this section.

Does this answer your questions?

Kevin

MJJ79

Yes and no. I really am a novice when it comes to script. I've added the CDN link to my page, and then items 2 and 3 listed above within the same script, which doesn't work? So obviously I need to place it somewhere else?

new DataTable('#points-augsep', {
    layout: {
        topEnd: null
    },
    info: false,
    ordering: true,
    paging: false,
    order: [[1, 'asc'], [12, 'desc']],
    columnDefs: [
        { visible: true, targets: 0 },
        { orderable: false, targets: 5 },
        { orderable: false, targets: 6 },
        { orderable: false, targets: 7 },
        { orderable: false, targets: 8 },
        { orderable: false, targets: 9 },
        { orderable: false, targets: 10 },
        { orderable: false, targets: 11 },
]
});

DataTable.enum([{
    "GK": 0,
    "DF": 1,
    "MD": 2,
    "FD": 3,
    "-": 4
}]);

kthorngren

I don't think its clear in the blog instructions but what you want is an array in the order of the sorting, for example:

DataTable.enum(["GK", "DF", "MD", "FD", "-"]);

Also make sure to execute the DataTable.enum() statement before initializing Datatables. Like this:
https://live.datatables.net/fokoxisu/1/edit

Kevin

MJJ79

Thank you so much. We've cracked it.