SearchBuilder conditions on 2 columns at the same time
SearchBuilder conditions on 2 columns at the same time
trongart
Posts: 222Questions: 51Answers: 0
If I have two numerical columns for example, is there a way to use the SearchBuilder to add a condition where "column 1 > column 2" or can SearchBuilder be used only on one column at a time?
If not, is there a way to filter "column 1 > column 2"?
This question has accepted answers - jump to:
Answers
Yep, you're right, there's no relative conditions, each condition is tied to a single column. That said, it is a nice idea, so I've raised it internally as enhancement request (DD-2308 for my reference), and we'll report back here when there's an update. It won't be in the near future due to other priorities, I'm afraid.
Colin
Thank you @colin- Look forward to having this feature sometime in the future!
Are there maybe any other ways to compare two columns?
You could do something like this. It's adding a custom filter whenever the button is pressed which compares the first letter of the Name compared to the first letter of the Position. You could do something similar with your numeric columns,
Colin
Thank you @colin!
@colin In this example I have 3 custom filters as you suggested: live.datatables.net/jasetuli/1/edit
I understand that this function removes any last custom search: $.fn.dataTable.ext.search.pop();
Is there a way to remove a specific custom search such as from filter 1 and from filter 2 and 3 specifically? So when a specific button becomes inactive, its specific search should be removed and not just any last search.
Is there also a function that removes all custom searches from all filters at once?
Basically the push() and pop() is adding or removing the search plugin from an array of searches. Instead of pushing and popping the search plugins I would create one search plugin that is initialized before the Datatables initialization. In the plugin use a series of if statements with
button().active()to see if the button is active. if it is then execute that search.Kevin
@kthorngren Thank you very much for your suggestion! Is there maybe an example I could refer to? Would it be possible to remove specific searches by deactivating the filters this way?
Assuming you want an OR search between all the active buttons I created this example:
http://live.datatables.net/jasetuli/2/edit
The easiest way to test is o click Filter 1 then Filter 3. You will see Bradley show when clicking 3.
The logic can be changed if you want an AND search.
Kevin
@kthorngren Thank you so much for this! I understand the approach, but how would you change the logic to have an AND search?
return button0 && button1 && button2
produces no results when a button is active
Something like this:
http://live.datatables.net/jasetuli/4/edit
Kevin
@kthorngren Appreciate your help! It works well, but unfortunately with this approach, the filter speed decreased 10x in my project (which has many other filters and buttons etc.). Adding the custom $.fn.dataTable.ext.search.push(...) function slows it down drastically.
However, the original approach from @colin did not have any impact on the performance. The only issue is that $.fn.dataTable.ext.search.pop() removes ANY last search that is applied from the filters and it does not work correctly when active buttons are deactivated in a different order than originally activated.
In my test case with 3 filters, http://live.datatables.net/jasetuli/1/edit, is there maybe a way to assign an index for every filter that is activated with $.fn.dataTable.ext.search.push() and to remove it through $.fn.dataTable.ext.search.pop() instead of removing just any last search?
Its likely you can make the code more efficient. I didn't spend much time with it - just wanted to give you a sample.
$.fn.dataTable.ext.searchis an array of search plugins. You can use standard Javascript methods like splice() to remove elements (plugins) from the array. Of course you will need to keep track of the order the plugins are pushed onto the array.Kevin
@kthorngren Appreciate the feedback! From the documentation, I tried using $.fn.dataTable.ext.search.indexOf() to get the index of the filter applied in $.fn.dataTable.ext.search.push() and to later remove it when the filter becomes inactive with $.fn.dataTable.ext.search.splice(index, 1). However, $.fn.dataTable.ext.search.indexOf() only gives me -1. Not sure where to go from here to find the relevant indices.
Unfortunately, I could not find more examples on the forum for this to see how to splice specific search plugins from $.fn.dataTable.ext.search or how to keep track of their order.
I'm not sure how you would use indexOf() for this particular case, since the array element is a function. I was thinking that a global variable could be used.
One option might be an array that you push the button index when active and when inactive find the position of the button index in the array, remove it from the array and that position should be the same in the
$.fn.dataTable.ext.searcharray.Kevin
This works! Thank you very much for your guidance!!
@colin When you get a chance, could you have a look at this example from your original test case as I believe something is not right here: live.datatables.net/fixuzoca/1/edit
The filter should show only results where Age >= Salary, but it does not do so correctly. It should show only 1 result instead of so many.
Debugging the code you can see the Age and Salary columns are string data not numeric. You can convert them to numbers. See this example for debug output and the conversion to numbers:
http://live.datatables.net/xogecoju/1/edit
Kevin