SearchBuilder conditions on 2 columns at the same time

SearchBuilder conditions on 2 columns at the same time

trongarttrongart Posts: 222Questions: 51Answers: 0
edited September 2021 in SearchBuilder

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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • trongarttrongart Posts: 222Questions: 51Answers: 0
    edited September 2021

    Thank you @colin- Look forward to having this feature sometime in the future!

    Are there maybe any other ways to compare two columns?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    Answer ✓

    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

  • trongarttrongart Posts: 222Questions: 51Answers: 0

    Thank you @colin!

  • trongarttrongart Posts: 222Questions: 51Answers: 0

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

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited October 2021

    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

  • trongarttrongart Posts: 222Questions: 51Answers: 0

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

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • trongarttrongart Posts: 222Questions: 51Answers: 0

    @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

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Something like this:
    http://live.datatables.net/jasetuli/4/edit

    Kevin

  • trongarttrongart Posts: 222Questions: 51Answers: 0
    edited October 2021

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

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    the filter speed decreased 10x in my project

    Its likely you can make the code more efficient. I didn't spend much time with it - just wanted to give you a sample.

    The only issue is that $.fn.dataTable.ext.search.pop() removes ANY last search that is applied from the filters

    $.fn.dataTable.ext.search is 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

  • trongarttrongart Posts: 222Questions: 51Answers: 0
    edited October 2021

    @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.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    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.search array.

    Kevin

  • trongarttrongart Posts: 222Questions: 51Answers: 0

    This works! Thank you very much for your guidance!!

  • trongarttrongart Posts: 222Questions: 51Answers: 0
    edited October 2021

    @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.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    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

Sign In or Register to comment.