Can't sort on 1 specific column
Can't sort on 1 specific column
Seanvbeurden
Posts: 5Questions: 2Answers: 0
I use PHP Server Side (Ozdemir Library) for the back-end of the table:
SELECT
l.LK_ID as id,
d.LK_ID as secondid,
LK_KLANTNR,
LK_KYC as KYC_STATUS,
p.name as LK_AFFILIATE,
LK_NAAM,
LK_NAAM as LK_BEDRIJFSNAAM,
......
//loads more data
FROM `licenties` l
INNER JOIN `partners` p
on l.LK_AFFILIATE = p.id
INNER JOIN details d
ON d.LK_ID = l.LK_ID
ORDER BY l.LK_KLANTNR DESC
The front-end uses typescript:
.DataTable({
serverSide: true,
searchDelay: 1000,
processing: true,
responsive: true,
autoWidth: false,
// editor: editor,
rowId: "id",
dom: "Bfrtip",
ajax: "datatables/clients.php",
columns: [
{
data: "id",
visible: false,
searchable: false,
orderable: false,
},
{
data: "LK_KLANTNR",
responsivePriority: 2,
},
{
data: "LK_NAAM",
responsivePriority: 3,
},
{
data: "LK_PLAATS_BEDRIJF",
responsivePriority: 6,
},
{
data: "LK_AFFILIATE",
responsivePriority: 5,
},
{
data: "KYC_STATUS",
// biome-ignore lint/suspicious/noExplicitAny: no typing available or known
render: (data: string, type: string, row: any, meta) => {
//render translations and type checks
}
},
responsivePriority: 1,
},
{
data: "LK_KLANTNR",
// biome-ignore lint/suspicious/noExplicitAny: no typing available or known
render: (data: string, type: string, row: any) => {
//render tooltips
},
responsivePriority: 4,
},
],
order: [[1, "desc"]],
// rowGroup: {
// dataSrc: "LK_AFFILIATE",
// },
language: {
url: locale.datatablesTranslationLink(),
},
select: {
style: "single",
},
buttons: [
//lots of buttons and functions
],
When I use set the order voor column 0 or 2 it works fine. But somehow when trying the same for column 1 it seems to go back to basic behaviors.
The object I get returned for a row looks as follows in the inspect browser tab:
{
"id": "18143",
"secondid": "18143",
"LK_KLANTNR": "18143",
"KYC_STATUS": "KYC_COMPLEET",
"LK_AFFILIATE": "ERA IT B.V.",
"LK_NAAM": "De Specialisten Groep B.V.",
"LK_BEDRIJFSNAAM": "De Specialisten Groep B.V.",
"LK_STATNAAM": "De Specialisten Groep B.V.",
//lots more properties
}
The id can be the same as the LK_KLANTNR but also different (depending on the system that created the data in the first place) so I am supposed to change the order of the rows to follow the LK_KLANTNR as opposed to the id.
Is the problem in the fact that I am trying to sort server-side data?
I have tried changing the rowId to LK_KLANTNR and changing the SQL ORDER BY to GROUP BY. As you can see I have disabled the rowGroup. When I try to sort on column 1 it always seems to group the data for the LK_AFFILIATE field with or without the rowGroup statement and sort on id ascending. This is not the case when trying to sort on column 0 or 2. Again seems to be the basic behaviour of the SQL statement.
Sorry for any spelling mistakes, English is not my native language.
Answers
When you have server side processing enabled there is no client side sorting taking place. The order of the data received in the JSON response is what Datatatbles will display at the client.
It doesn't look like your SQL query fully supports server side processing. For example you have hard coded
ORDER BYwhich negates the use ability to sort by clicking a column. Also I don't see the use of LIMIT and OFFSET to control the page returned. Do you needserverSide: trueenabled? See the server side processing info to help determine if its needed. Maybe try commenting it out to see if performance is still good to let sorting, searching and paging to be handled client side.Kevin
If you do need server side processing then maybe consider using the server side Editor library as described in this blog.
Kevin
I have the Editor library in use to process changes (creating, editing and deleting). But the reading is with this library. I can sort columns by clicking it. I have turned off the server side true statement. It is now very slow so I am going to try to use LIMIT and OFFSET.
LIMIT and OFFSET are intended to be used with server side processing to fetch only the data for the page. Your code snippet isn't enough to understand how that code supports server side processing which is why a responded with LIMIT and OFFSET. However if it works as is then there is probably something I'm not seeing.
The main point is that server side processing sorting is the responsibility of the server script. You may need to debug the SQL query if the sorting of that column is not as expected.
Kevin
Turning off the server side seems to work in terms of sorting but when submitting an edit to the back-end it hiccups in another way. The modal keeps loading. I checked the responses between the two and those are the same. Does it expect a different kind of response when serverside is false?
No the Editor doesn't expect a different response. See the Client / server docs for the expected responses.
Check the browser's console for errors. Let us know what you find.
Kevin
I have turned serverSide back on, partially because the work arounds are not viable and waiting till the whole table is done loading is not what my client wants. I have found that the problem is not within the data itself. Instead if I switch columns around the first visible column still isn't sortable, independent from the data that needs to be sorted.
https://datatables.net/forums/discussion/74389/ordering-sorting-on-first-column-0-not-working
This seems to be the same problem. It also seems to be the standard way the data is sorted if something else goes wrong (apart from the first column not working). It seems to be a problem only when serverSide is enabled, but I need this property not only for speed but for correct dataflow.
With server side processing enabled the sorting happens with the server side script. Datatables will display the rows in the order received in the JSON response. You can use the browser's network inspector to compare the order received with the order displayed to confirm this.
Is this the Ozdemir library you are using for server side processing? This is a third party developed library and support for it will need to come from their support resources. There isn't a knowledge base on this forum for that library.
Kevin