2 decimals number sorting

[hadiarakos]

Hello . Im very exciting for using this wonderful plugin but have problem with sorting numbers for example

thank you and keep up the good work

[RobertoPrevato]

Hi, @hadiarakos, Thanks for your interest in my library and your kind words.

The problem you are describing is quite common: in your screenshot the numbers are sorted by alphabetical order, while it's desirable to have them sorted by numeric value.

My library supports handling these situation, but it requires to keep the numbers in numeric format, and to define a formatter for the column, for example:

      // add the `money` custom type, defining a formatter:
      _.extend($.KingTable.Schemas.DefaultByType, {
        money: function (columnSchema, objSchema) {
          return {
            format: function (value) {
              // TODO: implement your formatting logic here (for thousands separators, decimal separators) - culture dependent
              // return a formatted string
              return "$ " + value;
            }
          };
        }
      });

      // adds reference to window, just for debugging purpose
      var table = window.kingtable = new $.KingTable({
        id: "operations-table",
        $el: $("#content"),
        data: [
          { name: "AAA", price: 40.40 },
          { name: "BBB", price: 60.50 },
          { name: "CCC", price: 20.20 },
          { name: "DDD", price: 30.30 },
          { name: "EEE", price: 30.35 }
        ],
        orderBy: "name",
        sortOrder: "asc",
        columns: {
          name: "Name",
          price: { name: "Price", type: "money" } // <-- specify that the `price` property is of `money` type
        }
      });
      table.render();

This way, the table uses the real value (numeric) when sorting, and displays the formatted string when building the HTML. The same situation arise when working with dates, which by default are represented in dd/MM/yyyy hh:mm format, but need to be sorted as real Date objects.

Please let me know if this helps.

As a side note, I just noticed that I need to improve my code, so that the formatting function receives the full item and not only the value to be formatted (let's say, if you have many currency codes coming together with the decimal amount). I can fix this tonight, in 8 hours time.

[RobertoPrevato]

Hi, @hadiarakos, Thanks for your interest in my library and your kind words.

The problem you are describing is quite common: in your screenshot the numbers are sorted by alphabetical order, while it's desirable to have them sorted by numeric value.

My library supports handling these situation, but it requires to keep the numbers in numeric format, and to define a formatter for the column, for example:

      // add the `money` custom type, defining a formatter:
      _.extend($.KingTable.Schemas.DefaultByType, {
        money: function (columnSchema, objSchema) {
          return {
            format: function (value) {
              // TODO: implement your formatting logic here (for thousands separators, decimal separators) - culture dependent
              // return a formatted string
              return "$ " + value;
            }
          };
        }
      });

      // adds reference to window, just for debugging purpose
      var table = window.kingtable = new $.KingTable({
        id: "operations-table",
        $el: $("#content"),
        data: [
          { name: "AAA", price: 40.40 },
          { name: "BBB", price: 60.50 },
          { name: "CCC", price: 20.20 },
          { name: "DDD", price: 30.30 },
          { name: "EEE", price: 30.35 }
        ],
        orderBy: "name",
        sortOrder: "asc",
        columns: {
          name: "Name",
          price: { name: "Price", type: "money" } // <-- specify that the `price` property is of `money` type
        }
      });
      table.render();

This way, the table uses the real value (numeric) when sorting, and displays the formatted string when building the HTML. The same situation arise when working with dates, which by default are represented in dd/MM/yyyy hh:mm format, but need to be sorted as real Date objects.

Please let me know if this helps.

As a side note, I just noticed that I need to improve my code, so that the formatting function receives the full item and not only the value to be formatted (let's say, if you have many currency codes coming together with the decimal amount). I can fix this tonight, in 8 hours time.

[hadiarakos]

Thank you for your quick answer.

I will try your suggestion and I will let you know about the results

Thank you again

-- Thales Chadiarakos

On 1 February 2017 at 14:01:29, Roberto Prevato (notifications@github.com) wrote:

Hi, @hadiarakos https://github.com/hadiarakos, Thanks for your interest in my library and your kind words.

The problem you are describing is quite common: in your screenshot the numbers are sorted by alphabetical order, while it's desirable to have them sorted by numeric value.

My library supports handling these situation, but it requires to keep the numbers in numeric format, and to define a formatter for the column, for example:

  // add the `money` custom type, defining a formatter:
  _.extend($.KingTable.Schemas.DefaultByType, {
    money: function (columnSchema, objSchema) {
      return {
        format: function (value) {
          // TODO: implement your formatting logic here (for thousands separators, decimal separators) - culture dependent
          // return a formatted string
          return "$ " + value;
        }
      };
    }
  });

  // adds reference to window, just for debugging purpose
  var table = window.kingtable = new $.KingTable({
    id: "operations-table",
    $el: $("#content"),
    data: [
      { name: "AAA", price: 40.40 },
      { name: "BBB", price: 60.50 },
      { name: "CCC", price: 20.20 },
      { name: "DDD", price: 30.30 },
      { name: "EEE", price: 30.35 }
    ],
    orderBy: "name",
    sortOrder: "asc",
    columns: {
      name: "Name",
      price: { name: "Price", type: "money" } // <-- specify that the `price` property is of `money` type
    }
  });
  table.render();

This way, the table uses the real value (numeric) when sorting, and displays the formatted string when building the HTML. The same situation arise when working with dates, which by default are represented in dd/MM/yyyy hh:mm format, but need to be sorted as real Date objects.

Please let me know if this helps.

As a side note, I just noticed that I need to improve my code, so that the formatting function receives the full item and not only the value to be formatted (let's say, if you have many currency codes coming together with the decimal amount). I can fix this tonight, in 8 hours time.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/RobertoPrevato/jQuery-KingTable/issues/8#issuecomment-276640860, or mute the thread https://github.com/notifications/unsubscribe-auth/APYBej9uZho-ZgmXcLvcieV_fgfwzCdtks5rYHQZgaJpZM4Lzk2o .

[hadiarakos]

I tried to add custom type via _.extend method but it looks this method is not extending the type.

for example.

_.extend($.KingTable.Schemas.DefaultByType, {
    money: function (columnSchema, objSchema) {
        return {
            format: function (value) {
                // TODO: implement your formatting logic here (for thousands separators, decimal separators) - culture dependent
                // return a formatted string
                return "$ " + value;
            }
        };
    }
});

var table = window.kingtable = new $.KingTable({
    id: "orders-table",
    $el: $("#orders-table"),
    // data: COLORS,//table is fixed!
    url: "/sales/api/renderJSOrders.php?user_id=" + id, //table is fixed!
    events: {
        "click .invoice": function (e) {
            var target = e.currentTarget.attributes;
            var id = target[0].nodeValue;

            // console.log(id);
            self.popitup("/sales/pages/invoiceMain.php?id=" + id, "Print invoice", "842", "595")
        }
        //this is the instance of KingTable
        //e is the instance of jQuery event
        //to obtain the clicked element, use e.currentTarget
    },
    collectionName: "Orders",
    orderBy: "webshop_order_id",
    sortOrder: "desc",
    columns: {
        TransNumber: {
            displayName: "Tranaction number",
            resizable: true,
            template: "<div style='padding: 5px;'><font  class='priceClick fancybox.ajax' href='/sales/pages/order_line_template.php?order_id={%print(ID)%}' data-id='{%print(ID)%}' style='font-weight: bold;color: #ba0600;'><span class='fa fa-eye'></span> {%print(TransNumber)%}</font> | <a data-id='{%print(ID)%}' class='invoice'>Print invoice</a></div>"
        },
        DateCreated: "Date Created",
        Price: {name: "Price", type: "money"}, // <-- specify that the `price` property is of `money` type
        VAT: {
            hidden: true
        },
        Shipping: {
            hidden: true
        },
        IP: "IP",
        ID: {
            hidden: true
        },
        webshop_order_id: {
            hidden: true
        }
    }
});
table.render();

Thank you for your time.

[RobertoPrevato]

@hadiarakos Sorry, today I forgot to tell you that I resolved a bug one week ago (at this commit): that was preventing custom types to work properly. Please update your file to the one published in the /dist folder of the repository, it should work then.

[hadiarakos]

Thank you. It seems is working now the extending method but unfortunately I get the same results. Is there a build in method to sort the column that is containing numbers Or I have to implement it ?

[RobertoPrevato]

You're welcome. I understand that the server is returning not actual numbers, but rather strings containing an amount together with a currency code, is my understanding correct?

In this case, if changing the server side code to return numbers is not an option, you will need to override a function to handle this particular situation, and write the sorting function that parses the strings extracting the numeric value, then compares numbers (example below).

// override the function with a new one
$.KingTable.prototype.sortClientSide = function (a) {
  // a is the array of items to sort
    var self = this,
        pag = self.pagination,
        sortBy = pag.orderBy,
        sortOrder = pag.sortOrder;
    if (!sortBy) return a;
    // get the column we are sorting by:
    var column = _.find(self.columns, function (o) { return o.name == sortBy; });
    // is of money type?
    if (column.type == "money") {
      // implement here your sorting logic (plain JavaScript)
      // when comparing values, a string like '$ 40.00' needs to be parsed as JavaScript number first,
      // then compared
    } else {
      // sort using the built-in function
      $.KingTable.Utils.Array.sortByProperty(a, sortBy, sortOrder);
    }
    return a;
}

// or, even better... instead of checking for "money" type, allow to use an ad-hoc column option
$.KingTable.prototype.sortClientSide = function (a) {
  // a is the array of items to sort
    var self = this,
        pag = self.pagination,
        sortBy = pag.orderBy,
        sortOrder = pag.sortOrder;
    if (!sortBy) return a;
    // get the column we are sorting by:
    var column = _.find(self.columns, function (o) { return o.name == sortBy; });
    // is of money type?
    if (column.sortFunction) {
      // sort using the column sorting function...
      // when comparing values, a string like '$ 40.00' needs to be parsed as JavaScript number first,
      // then compared
    } else {
      // sort using the built-in function
      $.KingTable.Utils.Array.sortByProperty(a, sortBy, sortOrder);
    }
    return a;
}

Let's say that your objects look like this:

var a = [{ price: "$ 50.00"}, { price: "$ 20.40"}, { price: "$ 10.40"}, { price: "$ 30.21"}, { price: "$ 40.50"}, { price: "$ 6.50"}, { price: "$ 140.50"}]

// a sorting function, in plain JavaScript, would be:
a.sort(function (a, b) {
  var priceOne = parseFloat(a.price.replace(/[^\d\.]/g, "")), 
      priceTwo = parseFloat(b.price.replace(/[^\d\.]/g, ""));
  if (priceOne > priceTwo) return 1;
  if (priceOne < priceTwo) return -1;
  return 0;
})

// to reverse:
a.reverse();

// Note that this example works only if the decimal separator is a dot. For example, in Italian culture it would be a comma and would need to be replaced with a dot.
// anything except digits and dots are removed using a regular expression

[RobertoPrevato]

@hadiarakos Sorry for writing so much! So, thanks to our conversation, I decided to add an extra option to the library. :)

Adding to my previous comment: I added support for 'sortFunction' column option, at this commit:

Now it's possible to specify the sort function used for each property. Otherwise, the code fallbacks to the default implementation.

Example (working, tested!)

// adds reference to window, just for debugging purpose
var table = window.kingtable = new $.KingTable({
  id: "operations-table",
  $el: $("#content"),
  data: [
    { name: "A", price: "$ 50.00"},
    { name: "B", price: "$ 20.40"},
    { name: "C", price: "$ 10.40"},
    { name: "D", price: "$ 30.21"},
    { name: "E", price: "$ 40.50"},
    { name: "F", price: "$ 6.50" },
    { name: "G", price: "$ 140.50"}
  ],
  orderBy: "name",
  sortOrder: "asc",
  columns: {
    name: "Name",
    price: {
      name: "Price",
      sortFunction: function (a, b) {
        // NB: return in ascending order (array is reversed automatically for descending order)
        var priceOne = parseFloat(a.price.replace(/[^\d\.]/g, "")),
            priceTwo = parseFloat(b.price.replace(/[^\d\.]/g, ""));
        if (priceOne > priceTwo) return 1;
        if (priceOne < priceTwo) return -1;
        return 0;
      }
    }
  }
});
table.render();

[hadiarakos]

Hello And Thank you Again for all your help!!  I will try the latest version of this helpful library. I send you also the data structure that I created to populate the table data. 

[   [     "DateCreated",     "Price",     "VAT",     "Shipping",     "IP",     "TransNumber",     "ID",     "webshop_order_id"   ],   [     "2017-01-23 15:46:03",     "14.50",     "0.00",     "0.00",     " 2.84.187.67 ",     "1100000182",     "147524",     "RES00001"   ] ]

As you can see the format of the price is in double with 2 decimals.

Thank you again 

Thales P. Hadiarakos | T : (+30)  6945683603| T :  (+30)  2109803523  M : hadiarakos@gmail.com | M : thch@mindtv.eu

On 2 February 2017 at 01:03:58, Roberto Prevato (notifications@github.com) wrote:

@hadiarakos Sorry for writing so much! So, thanks to our conversation, I decided to add an extra option to the library. :)

Adding to my previous comment: I added support for 'sortFunction' column option, at this commit - please download again the last version:

Now it's possible to specify the sort function used for each property. Otherwise, the code fallbacks to the default implementation.

Example (working, tested!)

  // adds reference to window, just for debugging purpose
  var table = window.kingtable = new $.KingTable({
    id: "operations-table",
    $el: $("#content"),
    data: [
      { name: "A", price: "$ 50.00"},
      { name: "B", price: "$ 20.40"},
      { name: "C", price: "$ 10.40"},
      { name: "D", price: "$ 30.21"},
      { name: "E", price: "$ 40.50"},
      { name: "F", price: "$ 6.50" },
      { name: "G", price: "$ 140.50"}
    ],
    orderBy: "name",
    sortOrder: "asc",
    columns: {
      name: "Name",
      price: {
        name: "Price",
        sortFunction: function (a, b) {
          // NB: by default, return in ascending order (array is reversed automatically for descending order)
          var priceOne = parseFloat(a.price.replace(/[^\d\.]/g, "")),
              priceTwo = parseFloat(b.price.replace(/[^\d\.]/g, ""));
          if (priceOne > priceTwo) return 1;
          if (priceOne < priceTwo) return -1;
          return 0;
        }
      }
    }
  });
  table.render();

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[hadiarakos]

Great! I change the Price value from the server to string " $ 10.40 " and with your latest example is working like a charm!

Thank you for your Help and your Support and thank you again for this master piece!

[RobertoPrevato]

@hadiarakos You're welcome! It was a pleasure to help :) I have a last recommendation: are you sure that you want to sort on the client side? The kind of table you are handling looks like will grow really big over time, in which case you'll need to make sorting and filtering on the server side (e.g. SQL Server, or MySQL). Client side sorting should be used only for small tables that don't require server side pagination. I wrote about this in this wiki page: https://github.com/RobertoPrevato/jQuery-KingTable/wiki/Working-modes.

Kind Regards, Roberto

[hadiarakos]

Is it possible to send me an example of server pagination and filtering. Is this data structure that I send you valid ?

Example: [ [ "DateCreated", "Price", "VAT", "Shipping", "IP", "TransNumber", "ID", "webshop_order_id" ], [ "2017-01-23 15:46:03", "$ 14.50", "$ 0.00", "$ 0.00", " 2.84.187.67 ", "1100000182", "147524", "RES00001" ] ]

[RobertoPrevato]

Yes, the structure is valid: you will have similar problems with dates (you need to parse the strings with dates into instances of Date objects, to sort them properly). I recommend to parse the values once, when data is fetched from the server side (if you are using jQuery ajax, I would use a custom converter for text json.

An example of server side pagination depends on the persistence layer you're using. For example, with SQL Server I would recommend to use a stored procedure and to use ROW_NUMBER() OVER (ORDER BY ..) to paginate results directly in SQL (there are also other two alternatives to ROW_NUMBER OVER); in MySQL I would use a stored procedure with LIMIT x OFFSET y). Right now I don't have examples that I can share; but one of next days I could publish two on the wiki.

[hadiarakos]

Hello again . 

Thank you again for your help and I will wait for your helpful examples.

Thales. On 2 February 2017 at 23:28:12, Roberto Prevato (notifications@github.com) wrote:

@hadiarakos Yes, the structure is valid: you will have similar problems with dates (you need to parse the strings with dates into instances of Date objects, to sort them properly). I recommend to parse the values once, when data is fetched from the server side (if you are using jQuery ajax, I would use a custom converter for text json.

An example of server side pagination depends on the persistence layer you're using. For example, with SQL Server I would recommend to use a stored procedure and to use ROW_NUMBER() OVER (ORDER BY ..) to paginate results directly in SQL (there are also other two alternatives to ROW_NUMBER OVER); in MySQL I would use a stored procedure with LIMIT x OFFSET y). Right now I don't have examples that I can share; but one of next days I could publish two on the wiki.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[hadiarakos]

Hello, Finally I make it works with mysql and php as server side implementation. If you interesting I can send you the code examples to check them and add then to your Wiki just for to save you time of work.

[RobertoPrevato]

Hi again! That would be great, thank you! I would gladly add the examples on the wiki, of course I will credit you.

By the way, in my free time I am working on the second version of the KingTable library, which will be much better, featuring:

• code base in ES6, unit tested using Jasmine and Karma
• plain vanilla javascript: removed dependencies from jQuery, Lodash and my R.js
• LRU caching mechanism out of the box, which makes the table super-fast also when loading data from the server side (or file system)
• logic to fetch custom list data (e.g. required information to render a custom filters view)
• core code even more abstracted from DOM manipulation and AJAX requests, making it suitable for desktop applications using Electron framework or other Node.js applications (for example, console applications displaying items on the console)

[hadiarakos]

Hello :-) 

All these are great features!!

Your library is by far the best jQuery table on the Internet . 

I will prepare the code and I will send it. 

Thank you for everything.

Thales.

On 3 February 2017 at 12:44:40, Roberto Prevato (notifications@github.com) wrote: Hi again! That would be great, thank you! I would gladly add the examples on the wiki, of course I will credit you.

By the way, in my free time I am working on the second version of the KingTable library, which will be much better, featuring:

code base in ES6, unit tested using Jasmine and Karma plain vanilla javascript: removed dependencies from jQuery, Lodash and my R.js LRU caching mechanism out of the box, which makes the table super-fast also when loading data from the server side (or file system) logic to fetch custom list data (e.g. required information to render a custom filters view) core code even more abstracted from DOM manipulation and AJAX requests, making it suitable for desktop applications using Electron framework or other Node.js applications (for example, console applications displaying items on the console) — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[RobertoPrevato]

Hi Thales, Thank you once more for your words! :) I received your email, I will add the example soon.

Roberto

[hadiarakos]

Hello 

Please replace the current file with this new one. 

I notice tha I had a bug with total numbers of rows while user is searching.  Now its fixed

the path is:  /server/

Thank you. 

Thales P. Hadiarakos | T : (+30)  6945683603| T :  (+30)  2109803523  M : hadiarakos@gmail.com | M : thch@mindtv.eu

On 3 February 2017 at 16:22:00, Roberto Prevato (notifications@github.com) wrote:

Hi Thales, Thank you once more for your words! :) I received your email, I will add the example soon.

Roberto

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.