Sorting through a custom column

Sorting through a custom column

javismilesjavismiles Posts: 205Questions: 38Answers: 3

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 :)

This question has accepted answers - jump to:

Answers

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    You will want to use Orthogonal Data.

    Kevin

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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 :)

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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" ?

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

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

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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;
    }
    },

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    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

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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,

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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?

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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?

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    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

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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?

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin
    Answer ✓

    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

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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?

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735
    Answer ✓

    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

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin
    Answer ✓

    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

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    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

This discussion has been closed.