Hi, Scott.

Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.

-

I am honestly not sure at all if we have a valid solution for this use-case with our current plugin's capabilities.

We will ask our 2nd level Team if they know of any possible workaround that we might suggest.

As soon as they respond we will report back, and i will let you know if we need further details from your end.

Thank you for your patience.

Hi, Scott.

We checked, and with our current plugin capabilities, we don't have an easy built-in solution to achieve this kind of sorting,

because in String columns, they get stored in the database as "VARCHAR" data type and have alphabetical sorting,

while in your use-case, you need the sorting to behave as for numerical data.

Some custom workaround ideas can be found online.

For example, on this page, you can see how you can try a 'PARSENAME' SQL function to get the different parts of an IP address value.

( For this to work, you will need an SQL Query based table)

Their initial SQL table output was like this :

IPAddress       
--------------- 
10.0.0.1
192.120.40.243
207.46.199.60
255.255.0.0
255.255.255.255
64.233.188.15
65.54.152.142
65.97.176.172
68.142.197.0
98.123.251.21

Then in their example of the following 'SELECT' statement will return the IP addresses sorted by their numeric value instead of by the 'VARCHAR' value:

SELECT [IPAddress] FROM [WorkStation]
ORDER BY CAST(PARSENAME([IPAddress], 4) AS INT),
         CAST(PARSENAME([IPAddress], 3) AS INT),
         CAST(PARSENAME([IPAddress], 2) AS INT),
         CAST(PARSENAME([IPAddress], 1) AS INT)

The output of this SELECT statement is the following, which sorts the IP addresses as required.

IPAddress       
--------------- 
10.0.0.1
64.233.188.15
65.54.152.142
65.97.176.172
68.142.197.0
98.123.251.21
192.120.40.243
207.46.199.60
255.255.0.0
255.255.255.255

-

But, our plugin is based on an SQL Parser, which has an issue with sorting if you use "ORDER BY" as part of an SQL Query based table directly from our plugin,

because wpDataTables plugin is adding "ORDER BY" dynamically based on what is set to be the default sorting column.

And I'm not sure if 'PARSENAME' is going to work well directly in our SQL parser in this use-case.

So my advice will be, try preparing a MySQL VIEW (which will return the data that you need), call it e.g. “view1” and then build a wpDataTables based on a simple query like

 "SELECT * FROM view1″.

 If you need help with using a VIEW in our plugin, you can see our video, where we show an example.

-

Please note: Using the SQL plugin feature requires at least a basic knowledge of SQL. It is assumed that you can create the table in some MySQL data manager (e.g., PHPMyAdmin, MySQL Workbench), and prepare a SQL query that will return the data you need. 

Please be advised that writing custom SQL Queries or debugging Queries does not fall under what our support covers.

You can check out this official page of WP codex,  for help with writing custom Queries.

Let us know if that helped.

If you get the output as intended, but if you see any issue with sorting on the resulting table in our plugin,

you can try to disable the Sorting option for that wpDataTable and then users on front-end won't be able to change the sorting, but it should sort properly on initial load.

I am not 100% sure if all of this will work perfectly, but if you try, let me know how it goes,

we will do our best to advise you as much as possible.

Thank you

Hey, Scott.

Thank you so much for sharing these possible solutions with us, and other users who might come across this ticket can use it as well.

Of course, please also don't hesitate to open new tickets for anything else that might come up,

as we like to keep one subject per ticket for more effective support.

Thanks again!