Question/Assistance Is there a possibility to use > or < expression as part of column filters?

[just10minutes]

Hi Thank you for this wonderful plugin.

I have below question, is there a possibility I can implement expression filters like > or < symbols?

for eg: Give me all customers whose age is greater than 20

Currently I have a filter search on top of Age column which gives me results which is equal to the search value.

How can I implement a option search like >20

Thanks

[just10minutes]

Some how I tried to apply the logic on my querySet by saying if the first character in search.value = > or < then use __gt or __lt search expression, but the moment the user types this text this on search field on front end they get a error saying this is an invalid Integer, and I know why that happens because that column is integer.

Do we have any alternative for this?

Thanks

[Benjamin Riddell]

I've done similar things by overriding the filter_queryset method.

I suspect you'll need to write a bit more though.

Back end you'll have to make sure that you correctly handle each request. If the search string is "<10" you'll need to make sure you take a slice using "[1:]" and then cast this to an int. You might need to consider what affects whitespace might have as well like "< 10". If a request is made for each change to the search input then you'll need to account for partial searches. e.g. what happens for each request in the following sequence:

1. "<"

2. "< "

3. "< 1"

4. "< 10"

Front end, you might have to sacrifice the integer validation by changing the input type to text. If you really want the validation then you'll have to write your own, probably want to attach it to the change event. If you want to go the extra mile you could keep the input type as number and add a dropdown to get this sort of effect https://getbootstrap.com/docs/4.0/components/input-group/#buttons-with-dropdowns Then prefix the selected value before the Ajax request is made using https://datatables.net/reference/event/preXhr

[just10minutes]

@WheatleyNZL Thank you for the detailed explanation. Yes you are right, I did try the method slice[1:]. Also as you pointed out I have to sacrifice Integer validation which I do not want to do.

Thank you for the dropdown approach, probably I will think of this approach.

Thanks

[Maciej Wisniowski]

What you can also do is to just add some custom fields to your web page, eg:

From: <input type="text" id="from"> To: <input type="text" id="to">

and then add override ajax -> data in datatables to pass its values to server, eg.:

var mydatatable = $('#mytable').DataTable({
    (...)
    ajax: { 
        (...),
       data: function(aoData){
          aoData.from = $('from').val();
          aoData.to = $('to').val();
          return aoData;
      }
 }

and add onchange handler to your fields and/or search form to trigger datatables reload on change, like:

$('#from').on('change', function(){
     mydatatable.ajax.reload();
     return false;
})

Then, in the backend, override filter_queryset method to use 'from' and 'to' values from the request for filtering.