Sorting through a custom column

javismiles

Good day,
in my table I have a column which doesnt read data from backend, it is used just to display the sum, the addition of the values of 2 other columns. I can sort the table according to each of those 2 other columns, but when I try to sort the table through that custom column that has the addition of the other 2, it gives error: unknown field ,

I really need to be able to sort the table according to that field, what should I do? thank u

kthorngren

You will want to use Orthogonal Data.

Kevin

javismiles

I think the problem is that its trying to pull the data from server for doing the sorting, but that column is using custom data from adding two other cells

javismiles

I See what you mean, thank u,
how can I specify the
sort - Data used for ordering
property to tell the cell to use its render content for sorting?
thats basically what i want, to use the rendered values for sorting

javismiles

right now im declaring the data of that cell like this

'data':function ( row, type, val, meta ) {
(row["spend"].amount==null) ? theamount=0 : theamount=row["spend"].amount;
(row["earn"].amount==null) ? theearn=0 : theearn=row["earn"].amount;
total=parseFloat(theearn).toFixed(2)-parseFloat(theamount).toFixed(2);
toadd=parseFloat(total).toFixed(2)+"€";
return toadd;
}

how can I specify that such result should be used also for sorting? thank u

javismiles

or if I put it in ColumnDefs

{
"targets": 18,
"data": "null",
"render": function ( data, type, row, meta ) {
total=parseFloat(row["worksol"].earn).toFixed(2)-parseFloat(row["worksol"].spend).toFixed(2);
(row["spend"].amount==null) ? theamount=0 : theamount=row["spend"].amount;
(row["earn"].amount==null) ? theearn=0 : theearn=row["earn"].amount;
// (row["worksol"].earn==null) ? theearn=0 : theearn=row["worksol"].earn;
total=parseFloat(theearn).toFixed(2)-parseFloat(theamount).toFixed(2);
toadd=parseFloat(total).toFixed(2)+"€";
return toadd;
}
},

how can I specify that the sorting value should be the same one I return in "render" ?

javismiles

not working so far, its still attempting to use the backend value to sort, rather than the rendered value

javismiles

I really dont get how to use this
data: {
_:
sort: ........
}

in data: or render:

to specify that I want the column to be sorted not through the backend but through the rendered values in that cell

{
"targets": 18,
"data": "null",
"render": function ( data, type, row, meta ) {
total=parseFloat(row["worksol"].earn).toFixed(2)-parseFloat(row["worksol"].spend).toFixed(2);
(row["spend"].amount==null) ? theamount=0 : theamount=row["spend"].amount;
(row["earn"].amount==null) ? theearn=0 : theearn=row["earn"].amount;
// (row["worksol"].earn==null) ? theearn=0 : theearn=row["worksol"].earn;
total=parseFloat(theearn).toFixed(2)-parseFloat(theamount).toFixed(2);
toadd=parseFloat(total).toFixed(2)+"€";
return toadd;
}
},

kthorngren

The Computed values example should help:

you can use if (type === 'sort') then put the value you want to sort by. You can place it in either columns or columnDefs.

Kevin

javismiles

I understand all that, read the page and all, I think Im not explaining well what my situation is sorry.

Look, there is no value in the backend that I want to sort by. No one.
That column is just taking values from two other columns and adding them, etc and displaying them there. So there is no "value" reference that I can reference that's my problem.

I already tried what you say but it doesnt work because there is no value to reference.
The value of that column is built on the fly from the values of two other columns,

javismiles

this example does not help and I explain why

{
data: 'start_date',
render: function ( data, type, row ) {
// If display or filter data is requested, format the date
if ( type === 'display' || type === 'filter' ) {
var d = new Date( data * 1000 );
return d.getDate() +'-'+ (d.getMonth()+1) +'-'+ d.getFullYear();
}

    // Otherwise the data type requested (`type`) is type detection or
    // sorting data, for which we want to use the integer, so just return
    // that, unaltered
    return data;
}

in that example in the case of "sort" it just returns data, the original data taken from backend. Again, I have no original data. That Cell has no original data source. The cell just takes data from other columns and aggregates them and then displays them on the cell

so how do I instruct datatables to take the output of the render as the sorting value?

javismiles

to specify even more, in my serverside:true project, it seems that sorting always tries to go sort through some value taken through the ajax, but in this specific column, there is no such thing, there is no original data of that column, that column just renders the addition of the values of two other columns,
so how do I indicate to datatables to please sort that column based on the value rendered on it, and not on any other original data value as such thing does not exist?

kthorngren

Sorry, didn't realize you were using server side processing. The sorting of this column is the responsibility of the server script since all of the rows for the rendered column is not in the client. There is no option to tell Datatables to sort a particular column based on client data when using server side processing.

Kevin

javismiles

ah.... I see......... thank you,
mmm thats a problem then for me, mmm,
and I cannot say, user server processing for these columns but not for this one?

javismiles

btw, if my table currently is small, do you advice that using client side is better?
If i have a table that may be very large in future but at the moment is small, what is the recommendation, to use client side now and change to server side when the table grows a lot? or to go directly with server side?
would be great to read more about pros and cons of serverside

javismiles

in any case I think I prefer server side true to be prepared and scalable from the beginning,

the thing is what do I do with a column that has no match on backend and its just a custom addition of other columns but I still need to have the table ordered by it

javismiles

if it must come from backend no matter what then the only possibility I see is making yet another mysql view that is the addition of two other views but not sure if that's possible

allan

and I cannot say, user server processing for these columns but not for this one?

No. Its either a server-side table, or its a client-side table.

btw, if my table currently is small, do you advice that using client side is better?

Yes. See the manual.

what is the recommendation, to use client side now and change to server side when the table grows a lot? or to go directly with server side?

If you know it will me tens of thousands or more records in future, then use server-side processing.

Allan

javismiles

Thank you Allan, and in client-mode, is it then possible to sort a column based on its rendered value rather than on data coming from the ajax calls?

kthorngren

In client mode Datatables will sort based on the data rendered into the column. If the displayed value is what you want to sort by then you don't need to do anything special. If you want to sort by something different than the displayed then you need to use orthogonal data.

Kevin

javismiles

thank you kthorngren, good to know,
a pity that client mode is only recommended up to a certain size, when you are expecting a project to grow very large it makes sense to go serverside from the beginning

allan

You might also be interested in reading over the orthogonal data section of the manual, which describes how you can display information that is different to what is sorted upon.

Allan

javismiles

yes Allan, Kevin also told me about that,
that´s useful yes but it didnt apply to my case, as in serverside it didnt work if u didnt have data coming from backend,

in any case I have now switched all to client mode as I dont expect people getting more than 1000 rows from my tables even if the tables grow huge,

so in client mode I dont have the issue anymore as sorting uses the displayed data and now all rows are sorting well, thank u again