Hello PrzemyslawGumulka.

Please tell me have You tried creating a VIEW for this table?

If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.

Best regards.

Hello again PrzemyslawGumulka.

Understood. The thing is that You created a new query from a view You created. In order for server-side tables to be able to calculate the sum You'd need to create another view for this table.

What You did is You created functions that You inserted into an existing view, and that's why the SUM and filtering doesn't work. As for special characters "Ł", "Ś", You said that the issue is resolved when You turn on server-side processing, so creating a new View should resolve both issues here.

Best regards.

Hello again Przemyslaw.

You're right, that seems to be an encoding issue with wpDataTables, and I'll forward this to our development team, so they can take a look at it.

I've noticed that when I open that table [wpdatatable id=10], Typ filter is predefined as W, but (as You said) nothing shows. However, when I click on the drop-down arrow and give it a few seconds, it loads the table (those 16 entries You mentioned). This behavior is very strange, so I must ask if there is a possibility to send us that table, zipped, so that we can import it in our PHPMyAdmin, create a view like You did and try it in our local environment?

Best regards.

Przemyslaw,

I apologize for late replies, it's just that I'm going over all the tickets one at a time. I'll prioritize this ticket until we get it resolved, I assure You.

We've managed to create tables, and generate the view just like You did, and the issue is present locally as well. We took the issue "under maintenance" so to speak, and I'll let You know as soon as we're done. I just wanted to let You know that we're working on finding the cause and resolving it as soon as possible.

I apologize once again for late response.

As far as e-mail notifications, I am not sure where to set this up and why it stopped working for You, You can contact Ticksy Support and see if there are any settings on Your side, or if there is something they have to do.

Best regards.

Hello again Przemyslaw.

Can you please send us the Query you used to create the view "viewDaneZwierzetaWojewodztwa"? We have to go through the code and change some things so characters "Ł" and "Ś" appear in the correct order.

We created a universal (English) sorting, because different users have a lot of special characters that are being used in a lot of different ways.

Best regards.

Hi again Przemyslaw.

I apologize, but I noticed that both sorting and SUM work good now with server-side turned on (see attachment). Did you change anything in the query?

I did notice that, when you change the number of rows displayed on the page, the SUM is still being calculated for all entries inside the table. I believe this is because you already used SUM in your query:

select `viewDaneZwierzeta`.`wojewodztwo` 
AS `wojewodztwo`,`viewDaneZwierzeta`.`typ` 
AS `typ`,`viewDaneZwierzeta`.`rok` 
AS `rok`,sum(`viewDaneZwierzeta`.`lis`) 
AS `lis`,sum(`viewDaneZwierzeta`.`zajac_sz`) 
AS `zajac_sz`,sum(`viewDaneZwierzeta`.`jelen_szl`) 
AS `jelen_szl`,sum(`viewDaneZwierzeta`.`sarna`) 
AS `sarna`,sum(`viewDaneZwierzeta`.`dzik`) 
AS `dzik`,sum(`viewDaneZwierzeta`.`bazant`) 
AS `bazant`,sum(`viewDaneZwierzeta`.`ges`) 
AS `ges`,sum(`viewDaneZwierzeta`.`kaczka`) 
AS `kaczka`,((sum(`viewDaneZwierzeta`.`jenot`) 
+ sum(`viewDaneZwierzeta`.`borsuk`) 
+ sum(`viewDaneZwierzeta`.`szakal_zl`) 
+ sum(`viewDaneZwierzeta`.`kuna`) 
+ sum(`viewDaneZwierzeta`.`norka_am`) 
+ sum(`viewDaneZwierzeta`.`tchorz`) 
+ sum(`viewDaneZwierzeta`.`szop_pr`) 
+ sum(`viewDaneZwierzeta`.`pizmak`) 
+ sum(`viewDaneZwierzeta`.`krolik`) 
+ sum(`viewDaneZwierzeta`.`los`) 
+ sum(`viewDaneZwierzeta`.`jelen_sika`) 
+ sum(`viewDaneZwierzeta`.`daniel`) 
+ sum(`viewDaneZwierzeta`.`muflon`) 
+ sum(`viewDaneZwierzeta`.`jarzabek`) 
+ sum(`viewDaneZwierzeta`.`kuropatwa`) 
+ sum(`viewDaneZwierzeta`.`grzywacz`) 
+ sum(`viewDaneZwierzeta`.`slonka`) 
+ sum(`viewDaneZwierzeta`.`lyska`)) 
AS `inne` from `viewDaneZwierzeta` 
group by `viewDaneZwierzeta`.`wojewodztwo`,`viewDaneZwierzeta`.`typ`,`viewDaneZwierzeta`.`rok`

Have you tried using calculated columns within wpDataTables, or just plain SUM for each column?

Best regards.

Hello again Przemyslaw.

I apologize, here's the screenshot from your page (Selection_108).

The thing with your table (with server-side turned on) is that the data doesn't show (like you said) when you load the page - it shows an empty table. But, when you just click on the filter "Typ" and move the mouse away from it - the table loads (please take a look at the screen recording, attached).

You can see that when the data loads, both the sorting and the sum is displayed correctly. This is very strange to us, as we haven't run into an issue like this - where you have a predefined filter value that shows a blank table until you just expand the filter.

Also, when I inspect the page and look at Network - admin-ajax finishes loading, and after it comes a select.png (just a drop-down arrow for filters). Then, when I expand the filter it sends out two new admin-ajax requests (Selection_115).

Now, this may be a conflict between our plugin and either the theme or another plugin. Please note that wpDataTables uses the WordPress' built-in jQuery version 1.12 and if your theme (or another plugin) use a newer version - it can cause an issue like this.

It's not wrong to use SUM in SQL per se, but I do believe that when you do - the sum for those columns doesn't change when you change the number of rows for the table. For example, in your table under Lis column the SUM stays 154.469 when you look at all the entries, and when you change to only 1 row.

Best regards.

Alright, Przemyslaw.

I'll forward this to one of our developers for debugging.

He's just asking for Query that has been used to create the `viewDaneZwierzeta` view. The view you used in `viewDaneZwierzetaWojewodztwa`.

I have the database you sent me, so I'll forward that to him, and when you reply with how that view was created he can start looking into the issue.

Best regards.

Hello again Przemyslaw.

Thank you for your patience.

We created everything locally, and found that the issue occurs when you use the DB name `viva63` when generating a query (or creating a view). So if you were to exclude it from the view, it should work normally - text shown in correct order, sum working correctly, and predefined filters should be loading normally - not with a blank table until you click away.

Please try this, and let me know if it works. If not, I can record the whole process from start to end, and show how it looks like on our side.

Best regards.

Hello again Przemyslaw.

This is a known issue that DataTables have a workaround for.

I invite you to take a look at this link, and I believe you'll be able to work around the issue when using non-server-side tables.

I hope this helps.

Best regards.

Hello again Przemyslaw.

Unfortunately no. We're using DataTables library and a lot of their functionalities for manipulating data, so in this aspect we depend on them, and like they said:

Sorting is one area where this has proven to be particularly difficult to get right, but fortunately with new Javascript APIs that are now available in browsers, we can get locale based sorting absolutely right every time.

Also, don't make the mistake of thinking that this post is only for developers who are working in languages other than English - the sorting options made available by the Intl API offer something for everyone!

Best regards.