Column Search Floating Point Numbers

curiou

I have a column of floats with two decimal points (e.g. "1001213.18", "12.58") and I can't seem to get column.search().draw() to work with this column. I'm using $.fn.dataTable.render.number() to show the values in the column as dollar amounts.

My hope is to be able to do something like the following:

// Create an array to store applicable column values
var arr = [];

//Iterate through column data and append to array if condition is met
for table.column(1, {search:"applied"}).data().each(function(cellValue) {
   if (condition) {
      arr.push(cellValue);
   }
}

// Join array values into one string and search/draw using regex
var searchValue = arr.join("|");
table.column(1).search(searchValue, true, false).draw();

This works for non-numeric/float columns but with this float column the search turns up empty (no results). I'm assuming it's a regex issue.

Any help appreciated, thanks.

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

curiou

Hi Colin,

Here is a test case: http://live.datatables.net/yesujasu/1/edit

Try typing into the fifth column ("Salary") and you'll see that no values are returned. The searchValue variable from the keyup function is correct (see console.log) but something is going wrong with column.search(searchValue, true, false).draw(). And the search function works fine for the first input box (in the Name column) that does not include floats.

kthorngren

Thats a big test case. Its not very usable for a test case so I removed all the unnecessary columns and just left a few rows. The idea of a test case is to show the problem you are having without a bunch of extra code and data that get in the way of looking at the problem.

I'm not sure what you are trying to do in the keyup event you have. I created a new simpler event. Both searching for the name and the updated floating point numbers works:
http://live.datatables.net/xarogawi/1/edit

Kevin

curiou

Hi Kevin,

Agreed on the test case size (sorry was reusing code)... In the test case you linked to, the search function does not work for either name or float, is there another link?

Thanks

curiou

Also, long story short, I need to keep conditionals (if/else) in the keyup event because of specific conditions that need to be met in my own code in order for a value to searched for in a column (different conditions/use case than what is shown in the test case). I was trying to keep my question focused on why column.search is not working with floats in my example. Below I have commented out my keyup function to make it clearer:

$('.keyup').keyup(function() {
    // The input element that had a keyup event
    var input = this;

    // Use data-index attribute to target column containing input element
    var idx = input.dataset.index;
    var column = table.column(idx);

    // Reset column search on each keyup
    column.search("").draw();

    // Create array to store all values matching the condition (different conditions/use 
    // case in my own code)
    var searchArray = [];

    // Loop through all column values to check if they match condition
    column.data().each(function(value) {
      // If input box is blank, reset search
      if (input.value === ""){
        column.search("").draw();
      // If the column cell value string starts with input box value and it is not already in 
      // the searchArray, then add it to the array
      } else if ((value.startsWith(input.value)) && 
                 (!searchArray.includes(value)) ) {
        searchArray.push(value);
      }
    });
    // Convert the array into a string, with each value to be searched separated by a
    // | as an OR logical operator
    var searchValue = searchArray.join("|");
    // Search/Draw
    column.search(searchValue, true, false).draw();
  });

To make it even less confusing, below is the same keyup function but I am simply front loading multiple values into column.search() before the draw instead of using the column.search("value1|value2|value3|..." ) method:

$('.keyup').keyup(function() {
    var input = this;
    var idx = input.dataset.index;
    var column = table.column(idx);
    column.search("").draw();
    column.data().each(function(value) {
      if (input.value === ""){
        column.search("").draw();

      // UPDATE HERE: Using column.search to front load values ahead of time instead of 
      // pushing the values to searchArray first and later passing them to column.search() 
      // as one string (e.g. "value1|value2|value3|...")
      } else if (value.startsWith(input.value)) {
          column.search(value);
      }
    });
    // UPDATE HERE: No need to use searchArray.join("|") because values are already 
       loaded via column.search(). 
    column.draw();
  });

For the above update example, column.search().draw() does not work either for the column containing floats.

curiou

Here is a test case for the above updated example: http://live.datatables.net/zohosola/1/edit
As you can see, column.search().draw() does not work here either for the column containing floats (but it works for the Name column that only contains text). There seems to be some sort of regex issue related to column.search and floating number values(?).

kthorngren

Sorry here is the link:
http://live.datatables.net/xarogawi/3/edit

I made a change for testing in the other and it caused the search to not work.

I'm still not clear on what you are wanting to do in the loop but the problem is you are executing column.search() for each row. You probably noticed in your big test case that the page became unresponsive, this is likely why.

Maybe, for your requirements, a search plugin might be more appropriate. It will loop through all the rows and you can decide if the row should be displayed (return true) or not (return false) based on the requirements.

Kevin

tangerine

I don't understand your example. You have two "Salary" columns.

curiou

Hi Kevin,

I see your example, but unfortunately I can't use smart search (I need to search from the starting character for all values) and I need to use conditions. I was initially using a search plugin but it became unwieldy given the size of my table. Given the number of columns I need this functionality applied to, the search plugin was doing a bunch of unnecessary calculations.

Could you explain a bit further what you mean by "executing column.search for each row"? I may have a fundamental misunderstanding of how the function works. Isn't the point of having .draw() execute the actual search to allow you to run column.search() multiple times before column.draw() in order to load multiple values before executing the search?

Thanks

curiou

Hi tangerine,
Sorry I know the initial example was too hefty, please use this example:
http://live.datatables.net/zakibixa/1/edit
Thanks

kthorngren

Could you explain a bit further what you mean by "executing column.search for each row"?

    column.data().each(function(value) {
      if (input.value === ""){
        column.search("").draw();  // This will search and draw for each column.data() 
              // If you have 100 rows this will execute 100 times.

      } else if (value.startsWith(input.value)) {
          column.search(value);   // This will execute a search for each iteration. 
              // Just like above it will happen for each row.
      }
    });

The search API is executed each time you call it. You don't have to draw the table. So you don't want to do this in a loop. Take a look at this example. It uses the search event to output each search API call. A for loop executes multiple searches. Then it outputs the current search value.
http://live.datatables.net/vayateya/1/edit

Note there is Ashton Cox3 and Ashton Cox4. The loop will search for both but when drawn only one will appear.

I need to search from the starting character for all values

You can use a regex search for this. Search the regex search in my exmple.

and I need to use conditions

This is what I'm not clear about. What are the conditions?

Kevin

curiou

The search API is executed each time you call it.

Ah gotcha, thanks for clarifying! I misread that part in the search() API documentation.

The regex in your test case is really helpful and is similar to my initial test case where I was doing (column.search('value1|value2|value3|...'). Similar to how you are using:

'^(Director|Architect)'

in your example, I need a way to figure out the proper string formatting for column.search() regex to recognize and search for floats with two decimal points. Something like:

'^(12384.12|62.01|543210.62)'

I understand how to do the regex matching with regular JS, but in the context of DataTables I am not sure what the correct string formatting would be?

If it helps, I changed my initial test case to use the regex from your example and it's working perfectly for the Name column, but not for the Salary column that contains the floats: http://live.datatables.net/doximepu/1/edit

See the third to last line of the keyup function in the test case:

var searchValue = '^(' + searchArray.join("|") + ')';

Thanks

curiou

Forgot this:

What are the conditions?

The conditions are less/greater than or equal to (e.g. >, <, >=, <=). I'm handing these conditions with regular pattern matching; in my own code, the keyup event is only for columns containing floats (there are no text-based "Name" columns, it's all numeric). As per the above, I am just hoping there is a particular string format I can use to get an exact match in datatables search for multiple float values, like

'^(12384.12|62.01|543210.62)'

Thanks

kthorngren

Sorry, I'm still not understanding the purpose of this loop:

    // Loop through all column values to check if they match condition
    column.data().each(function(value) {
      // If the column cell value string starts with input box value and it is not already in
      // the searchArray, then add it to the array
      if ((value.startsWith(input.value)) &&
          (!searchArray.includes(value)) ) {
            searchArray.push(value);
      }
    });

It seems like you are trying to build a regex string with specific values where the beginning of the string matches the input value. So if the input is 12 and the column contains 1234,1278,3412,4355 the result would be the array of [1234, 1278]. Then you are creating the regex string of ^(1234|1278). But doing the regex search of ^12 will result in the same rows. So looping though to get the values for the search is redundant. Maybe I'm not understanding.

One problem is you are prefixing the numbers with $ but you aren't accounting for that in the regex search. The regex string should look like this ^\\$12. Note the \\ is need because you need to escape the $ since its a regex operator. See the updated example:
http://live.datatables.net/doximepu/2/edit

You don't really need this code:

    // Reset column search on each keyup
    column.search("").draw();

Its an extra step that is not needed.

Kevin

curiou

I'm still not understanding the purpose of this loop

Like you're saying, the loop is really not needed in the use case of the example I provided. In the context of my own code however there is a second scenario where I need to adjust for whether the end user has entered a "<", ">=", etc. into the input box preceding the float, and it's easier to deal with this (on my end) using a loop with conditions and regular JS regex.

It seems like you are trying to build a regex string with specific values where the beginning of the string matches the input value.

Yes exactly, and I have yet to figure out the correct regex string for searching a datatables column for multiple decimal number values.

One problem is you are prefixing the numbers with $

In the test case, I'm using $.fn.dataTable.render.number() to make the column values look like dollar values (e.g. $128,132.35) whereas the actual data values are plain floats (e.g. 128132.35). Would I still need to consider the rendered dollar formatting in my regex when actual data values are regular floats? I tried adjusting my example per your example, but it doesn't seem to make a difference.

Its an extra step that is not needed

Agreed, thanks.

kthorngren

I'm using $.fn.dataTable.render.number() to make the column values look like dollar values (e.g. $128,132.35) whereas the actual data values are plain floats (e.g. 128132.35)

Use Orthogonal data to render the preceding $ for the display operation. Then you can ignore the $ and commas.

I need to adjust for whether the end user has entered a "<", ">=", etc

I think you would be better off using a search plugin. With your method you iterate the column data once to build the search term then Datatales iterates the column data to execute the search. You can accomplish the same with a plugin and only iterate once. Here is your example update to use a search plugin.
http://live.datatables.net/moxawase/1/edit

Basically I created a column type called float so multiple columns can use the same code. The plugin iterates the columns looking for the columns assigned the type float. For these columns it will process the rules you define for checking the values. All it does now is check the beginning of the cell data matches the input, effectively the same as the regex we are looking at.

You can add code to parse the string for <, >, etc and handle checking the values. The valid boolean variable determines if the row is displayed or not. Currently its setup as an AND search between all the columns. You can change how its handled if you want and OR search.

This Range Search example may give you some ideas.

Kevin

curiou

Use Orthogonal data to render the preceding $ for the display operation. Then you can ignore the $ and commas.

Thanks for this, it was the missing piece that made my original code work (i.e. I can now search for 'float1|float2|...")! I decided to stick to my keyup function instead of going with a search plugin because:

1) The search plugin runs every single time there is a draw() which seems more computationally expensive than having an event listener that only runs when the actual event occurs.
2) The search plugin approach runs on every single column that has a "float" type which seems more computationally expensive than iterating through 1 event triggered column twice (per my example). I have about 15 columns that have float types in my own code.

Am I misunderstanding on the above? Also, I don't think I'm following how the Range Search example runs an OR search?

Thanks

kthorngren

You make a good point about your keyup method versus the plugin which runs for each table draw. Glad you got it working.

I don't think I'm following how the Range Search example runs an OR search

Sorry, wasn't clear. I pointed you to the range search for ideas of how to do the <, > =<, >= filters with a search plugin.

Kevin