Is there a way to sort similar to MySQL order by field?

TomBajzek

I have a situation where I need to sort a table by a column whose content (which is limited to a small set of values) does not fall alphabetically into the desired order for display. The MySQL feature ORDER BY FIELD(...) would accomplish what I need when the page is loaded. I would need to be able to do this as the page might be re-sorted while it is being viewed. Does DataTables have a way to do this, client-side, that I've just not found?

kthorngren

Without knowing more about the data you want to sort I would suggest looking at Orthogonal Data and rendering something for the sort type/

Or you can see if there is a sorting plugin that might help:
https://datatables.net/plug-ins/sorting/

Or you can create your own sorting plugin:
https://datatables.net/manual/plug-ins/sorting

Kevin

kthorngren

I realized that I didn't totally grasp your question and took sone time to understand what FIELD() does. The question I have si how would you want to provide the FIELD() array to Datatables?

I started messing with it here:
http://live.datatables.net/banubete/1/edit

But its not quite right. Sorting ascending seems to work but when sorting descending it doesn't apply to fields array.

After looking at it I think a search plugin would be the way to go. If I get a chance I will see if I can put one together. Otherwise feel free to try

Kevin

kthorngren

Its been awhile since I've messed with an ordering plugins. Think I have the FIELD() function emulated here:
http://live.datatables.net/bukuguji/1/edit

The results should match what is found here:
http://sqlfiddle.com/#!9/fee8d0/1

Good way to end the week

Kevin

TomBajzek

Kevin,

Thank you for your response. I never expected that someone would take the trouble to write a plugin to address my question - that is outstanding!

However, while I think I see what your plugin is doing, I don't quite understand how to apply it to my case. I would like to apply this technique to the first of the sort columns that are used for this table. My table definition begins like this:

        var ticketTable = $('#tickets').DataTable( {
            order: [[4,'desc'],[2,'asc'],[0,'desc']],
                ...

It's column 4 where I'd like to apply this type of sorting, but I don't see how to apply this to a particular column.

Also, in studying your solution and DataTables plugins, I came across the enum sorting option, which looks a lot like what I want, but I don't understand how to apply that sorting method to my situation, either. Just a little more advice would be greatly appreciated.

Thanks,
Tom

allan

To apply a sorting plug-in to a specific column, use the columns.type option. In Kevin's example, he uses:

      {
        targets: 0,
        type: 'custom'
      }

to apply that custom sort to the first column (i.e. index 0).

Allan

kthorngren

I never expected that someone would take the trouble to write a plugin to address my question

I did that for selfish reasons so I could learn about the MySql filter function

The enum plugin you found seems simpler to implement.

Kevin

TomBajzek

Allan,

I had seen the use of custom in the documentation, but I did not find anything that defined the type custom, so I thought I needed to find a way to define it. Or, do I just need to replace custom with enum in this case?

Now, looking at what Kevin's plugin does, I realized that I can make this issue go away by looking up the sort order for the content of this field (it has only a half-dozen possible values, from a select element) and storing that as another field in the database when I store the record. Then I can just sort that column (provided I can sort on a hidden column) because I would not want to display that.

Also, related to Kevin's suggestion, the example of the order by field that I had in mind is simpler that what Kevin emulated. I thought I might include this example from another application (that does not use DataTables) here to illustrate what I had in mind originally:

order by field(status,
"unseen",
"in queue",
"in progress",
"awaiting award",
"on hold",
"pending approval",
"closed",
"lost")

That is the type of sort I wanted to achieve when I asked my question.

Thanks for your help with this,
Tom

kthorngren

I can just sort that column (provided I can sort on a hidden column) because I would not want to display that.

You can order the table using hidden columns. A couple options you may want to look at to facilitate this are columns.orderData and orderFixed.

But it does seem the enum plugin you noted is a good option. All you need to do is pass the above array. It seemed too simple to work so I tried it here:
http://live.datatables.net/jupuleti/1/edit

Kevin

TomBajzek

Kevin,

You make a good point here. It looks like enum is even easier (and clearer) that the solution I had in mind. I think I'll try that.

Thanks for being so helpful,
Tom

TomBajzek

Kevin,

I've tried to implement the enum solution as you suggested, but I've got something wrong that I can't figure out.

I have the following in my DataTable:

            columnDefs: [
                {
                    targets: [5,6],
                    orderable: false
                },
                {
                    targets: 4,
                    type: 'custom'
                }
            ],

Where column 4 is the column I want to sort via the enum.

I have the following code to define the enum for the sort-order:

        $.extend( $.fn.dataTable.enum( 
            [
                'urgent',
                'unseen',
                'in  queue',
                'holding',
                'maintenance',
                'closed'
            ] )
        );

I have included the plug-in in the HTML:

        <script src="https://cdn.datatables.net/buttons/1.4.1/js/buttons.colVis.min.js" type="text/javascript"></script>

The sort order is not as defined above, but is alphabetical, as it was before I inserted the code above.

Have I applied your suggestion incorrectly, or have I missed something else the is essential? (There are no Javascript error reported.)

(I'm sorry for coming back to this after several weeks have elapsed, but I was diverted onto some other projects in the interim, and have just got back to this.)

Thanks,
Tom

kthorngren

I think the problem is that you are assigning the type type: 'custom' to the column. I believe the plugin will scan each column and if all the cells in the column are in the array you defined then it will automatically assign the type of enum to the column. However if one or more cells don't match the array then the type won't be set.

Try removing type: 'custom'. Also, it looks like you have two spaces in 'in queue',. The scanning is probably an exact match.

The plugin you pasted is buttons.colVis.min.js. Maybe its a copy paste error but the plugin should be https://cdn.datatables.net/plug-ins/1.10.19/sorting/enum.js.

If this doesn't help then please post a test case with en example of your data.

Kevin

TomBajzek

Kevin,

Removing the type": "custom" from the sortable column fixed it. I appreciate your quick and helpful response.

Also, I did inadvertently paste in the wrong plugin. The correct one is there, on the following line.

As for the "in queue" having two spaces, that is a very astute catch on your part. However, the two spaces actually are in the data as a historical way of getting the correct sort order when the set of values in that field was defined differently than it is now, and before I had found other ways of handling this. My client's requirements change over time, sometimes in ways that create interesting problems in implementation.

Thanks for being so helpful,
Tom