sorting by a column taking into account all pages of pagination

darisz

Hello. I got a problem with datatables. I want to sort rows by a column with username. There is also a pagination. The problem is that the table is sorted only on the first page. The second page is not taken into account when sorting. In other places in my project datatables sorts well column through a few pages. But only in that one place it sorts wrong (only first page). The code is similar and I want to add that I use ajax source in the table which sorts wrong. Any help will be appreciated.

tangerine

http://datatables.net/forums/discussion/12899/post-test-cases-when-asking-for-help-please-read

darisz

It is hard for me to prepare test case because ajax and json and sql pagination is involved and it is just complex. Please help me anyway :) . Do you have any suggestions? I will wait for answer even longer than normally but please try to help me.

allan

To be honest, we can't help since we have no idea what is going wrong. As you will be able to see in all of the examples on this site DataTables does take the second, third, etc pages into account when sorting, so there is something going wrong on your page. But without being able to see it... Absolutely no idea what it is!

Allan

darisz

Thanks Allan for your reply. I just try to make my post more precise. The problem with the sorting is that sql pagination returns only part of the rows (for example 15) and datatables just cannot see more. My question is now: can I by clicking on the sorting indicator (small triangle) do (invoke) sorting on the serverside and then return rows (for example 15) from larger number of sorted rows?

darisz

Here is the example sql query:
[code]
SELECT DISTINCT a.displayname, a.username, b.profile_name , b.version, c.status FROM upm_applied_profiles a, upm_profiles b, upm_applied_status c WHERE a.base_profile_id = b.profile_id AND a.p_applied_id = c.p_applied_id AND c.status <= 17500 AND displayname IN ( SELECT displayname FROM ( SELECT ROWNUM AS rn, subtable.* from ( SELECT DISTINCT ap.displayname FROM upm_applied_profiles ap, upm_profiles ba, upm_applied_status ce WHERE ap.base_profile_id = ba.profile_id AND ap.p_applied_id = ce.p_applied_id AND ce.status <= 17500 ORDER BY ap.displayname ) subtable WHERE ROWNUM < 16 ) WHERE rn >= 1 ) ORDER BY a.displayname asc, a.username, b.profile_name
[/code]
The clause ORDER BY is set by variable (asc or desc whether request.getParameter("sSortDir_0") has appropriate value). I think that something is wrong with the query. Any thoughts? Previous post is answered by myself because if the sorting indicator is clicked it changes sorting order and the query. Please help :). Regards Allan.

darisz

Now I'm sure that this is the query. When sorting it takes into account only first page. Are you sql specialist also? If not sorry for bothering you but If you are please look at it to see what can be done to take all rows to sort not only one page. Thanks in advance!

darisz

I found the solution!!! in the first nested order by there should be order indicated also (desc or asc).
Thanks Allan. Greetings.