"not like" search

montoyam

Has anybody created "not like" search capability that works like the regular DataTables search box, where it searches all fields? I know I can add a custom text box and send the contents and use them in the ajax call like a parameter, but the where statement would have to have each field in it. I like how the built in searchbox looks at each field and would want something like that if possible. I'm thinking I can't be the first person to want this

kthorngren

I know I can add a custom text box and send the contents and use them in the ajax call like a parameter, but the where statement would have to have each field in it.

Are you using server side processing?

If using client side processing then take a look at using a Search Plugin.

Kevin

rf1234

For client side processing Kevin's advise is the best using $.fn.dataTable.ext.search.push which works great.

If you want to do server side searching in a proprietary manner using the where clause you can either use each field in the WHERE clause or you use what I've called a global search field.

In my case the "global search field" contains:
- language specifically rendered database content (e.g. if your database date is 2020-07-08 00:00:00 and the user enters 8.7.2020 or 08/07/2020 nothing is going to be found without rendering!)
- all of the contents of parsed documents the user uploaded before and / or that were made machine readable in a different step (i.e. pdf's, MSOffice etc. documents)

All of this is done using SQL views. In more complex cases it is even a hierarchy of views.

So this nice little view anticipates the user's search entries in German or English. The view renders the database content in various formats the user may enter at the front end if her language is either German or UK English. American date formats etc are not supported.

CREATE VIEW `ctr_searchable` AS

SELECT a.id                                     AS ctr_id,
       CASE WHEN a.purpose > ""               THEN CONCAT(a.ctr_name, ", ", a.purpose)
                                              ELSE a.ctr_name
       END                                      AS name_purpose_string, 
       DATE_FORMAT(a.start_date, '%d.%m.%Y')    AS start_date_german,
       DATE_FORMAT(a.start_date, '%d/%m/%Y')    AS start_date_english,
       DATE_FORMAT(a.end_date, '%d.%m.%Y')      AS end_date_german,
       DATE_FORMAT(a.end_date, '%d/%m/%Y')      AS end_date_english,
       DATE_FORMAT(a.original_end_date, '%d.%m.%Y') AS original_end_date_german,
       DATE_FORMAT(a.original_end_date, '%d/%m/%Y') AS original_end_date_english,
       CASE WHEN a.automatic_prolongation > 0 THEN "Ja"
                                              ELSE "Nein"
       END                                      AS automatic_prolongation_german,
       CASE WHEN a.automatic_prolongation > 0 THEN "Yes"
                                              ELSE "No"
       END                                      AS automatic_prolongation_english,
       -- CASE WHEN a.expired > 0 THEN "beendet"    ELSE "laufend"
       -- END                                       AS expired_german,
       -- CASE WHEN a.expired > 0 THEN "ended"     ELSE "ongoing"
       -- END                                       AS expired_english,
       CASE WHEN a.follow_up_days > 0       THEN 
            CONCAT(a.follow_up_days, " Tage")  ELSE
            "keine" 
       END                                      AS follow_up_days_german,  
       CASE WHEN a.follow_up_days > 0       THEN 
            CONCAT(a.follow_up_days, " days")  ELSE
            "none" 
       END                                      AS follow_up_days_english,            
       CAST(a.serial AS CHAR)                   AS serial,
       CONCAT(REPLACE(LEFT( FORMAT(a.annual_cost_income, 2), POSITION('.' IN FORMAT(a.annual_cost_income, 2)) - 1 ), ',', '.'), ',', SUBSTR(FORMAT(a.annual_cost_income, 2), POSITION('.' IN FORMAT(a.annual_cost_income, 2)) + 1))
                                                AS annual_cost_income_german,
       FORMAT(a.annual_cost_income, 2)          AS annual_cost_income_english,
       CAST(a.annual_cost_income AS CHAR)       AS annual_cost_income_english_short,
       REPLACE(a.annual_cost_income, '.', ',')  AS annual_cost_income_german_short,       
       CASE WHEN a.VAT_charged > 0 THEN ""
                                   ELSE "Keine"
       END                                      AS VAT_charged_german,
       CASE WHEN a.VAT_charged > 0 THEN ""
                                   ELSE "None"
       END                                      AS VAT_charged_english,
       CASE WHEN a.VAT_rate_reduced > 0 THEN "USt. ermäßigter Satz"
                                        ELSE "USt. voller Satz"
       END                                      AS VAT_rate_reduced_german,
       CASE WHEN a.VAT_rate_reduced > 0 THEN "VAT reduced rate"
                                        ELSE "VAT full rate"
       END                                      AS VAT_rate_reduced_english,
       CASE WHEN a.VAT_deductible > 0 THEN "USt. ist abzugsfähig"
                                      ELSE ""
       END                                      AS VAT_deductible_german,
       CASE WHEN a.VAT_deductible > 0 THEN "VAT is deductible"
                                      ELSE ""
       END                                      AS VAT_deductible_english,
       GROUP_CONCAT(DISTINCT COALESCE(c.dept_name, '') SEPARATOR ', ')      AS dept_name_string,
       GROUP_CONCAT(DISTINCT COALESCE(e.label_text, '') SEPARATOR ', ')     AS label_text_string,
       GROUP_CONCAT(DISTINCT COALESCE(f.category_name_value_string, "Bitte mindestens eine Kategorie auswählen.") SEPARATOR ', ')   
                                                                            AS category_name_value_string_german,
       GROUP_CONCAT(DISTINCT COALESCE(f.category_name_value_string, "Please select at least one category.") SEPARATOR ', ')     
                                                                            AS category_name_value_string_english,
       CASE WHEN a.follow_up_days > 0 THEN 
            GROUP_CONCAT(DISTINCT COALESCE(g.event_due_date_text_string_German, "Keine weiteren Ereignisse") SEPARATOR ', ') 
                                      ELSE
            GROUP_CONCAT(DISTINCT COALESCE(g.event_due_date_text_string_German, "Es sind noch keine Ereignisse definiert.") SEPARATOR ', ')                          
       END                                                                  AS event_due_date_text_string_German,
       CASE WHEN a.follow_up_days > 0 THEN 
            GROUP_CONCAT(DISTINCT COALESCE(g.event_due_date_text_string_English, "No further events") SEPARATOR ', ') 
                                      ELSE
            GROUP_CONCAT(DISTINCT COALESCE(g.event_due_date_text_string_English, "No events defined yet.") SEPARATOR ', ')                          
       END                                                                  AS event_due_date_text_string_English
                                                                
  FROM ctr a
  LEFT JOIN ctr_has_ctr_govdept b   ON a.id = b.ctr_id
  LEFT JOIN ctr_govdept c           ON b.ctr_govdept_id = c.id
  LEFT JOIN ctr_has_ctr_label d     ON a.id = d.ctr_id
  LEFT JOIN ctr_label e             ON d.ctr_label_id = e.id
  LEFT JOIN category_name_value f   ON a.id = f.ctr_id
  LEFT JOIN event_due_date_text g   ON a.id = g.ctr_id
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23

The next step is then to create a second view that is based on this view and other information in order to create the "global search field". If you are still interested in seeing this and the respective Editor code let me know ...

This terribly looking line is my favorite

CONCAT(REPLACE(LEFT( FORMAT(a.annual_cost_income, 2), POSITION('.' IN FORMAT(a.annual_cost_income, 2)) - 1 ), ',', '.'), ',', SUBSTR(FORMAT(a.annual_cost_income, 2), POSITION('.' IN FORMAT(a.annual_cost_income, 2)) + 1))
                                                AS annual_cost_income_german,

It renders a database amount 1000000.99 as 1.000.000,99.

To render that amount the English way the statement is much easier. This one renders the same database amount as 1,000,000.99

       FORMAT(a.annual_cost_income, 2)          AS annual_cost_income_english,

montoyam

I think I am going to go with the client side search extension. The example given in the documentation seems pretty straight forward. But since I am wanting to search all fields, I guess I would use something like data.includes(searchValue)

montoyam

one thing I don't understand looking at the example; I have multiple data tables on one page. I want this search to only work on one of the datatables. How do I do this?

kthorngren

See if this thread helps.

Kevin

rf1234

... or even easier: If your data tables on the same page have "row" variables that are unique to them you can check for them being "undefined" and hence make sure the search only applies to the one data table that you want. I use it that way but you can also apply different searches to each data table with this trick like in here (old code, could be done better - but it works ...).

$.fn.dataTable.ext.search.push(
    function( settings, data, dataIndex, row, counter ) {
        if ( typeof row.contract !== 'undefined' &&
             typeof row.contract_has_infoma == 'undefined' )   {
        //all non-manual contracts are filtered out
            if (row.contract.gov_controlled < '1') {
                return false;
            } else {
                if (row.contract.expired > '0') {
                    return filterShowNMonths(row.contract.exp_date);
                }
            }
        } else {    //not a contract row but a row of an element
            if ( typeof row.variable !== 'undefined' || typeof row.fixed !== 'undefined' ) {
                var selected = contractTable.row( {selected: true} );
                if (selected.any()) {
            //in case it is a prolongation the expired components are filtered
            //out by default (redraw upon select of contract raw and also upon push button)
                    if ( selected.data().contract.prolongation > '0' ) {
                        if ( typeof row.variable !== 'undefined' && 
                             typeof row.cashflow === 'undefined'    ) {
                            return filterExpElements(row.variable.end_date, 'VariableExpElementsButton', showVariableExpElements);
                        } else {
                            if ( typeof row.fixed !== 'undefined'&& 
                                 typeof row.cashflow === 'undefined'  ) {
                                return filterExpElements(row.fixed.end_date, 'FixedExpElementsButton', showFixedExpElements);          
                            }    
                        }
                    }
                }
            }
        }
        return true;
    }                
);

This thread may be helpful as well ... I had similar issues with this:
https://datatables.net/forums/discussion/60000

montoyam

rf, I think I get what you are doing there. unfortunately, my datasets look very identical to each other. You may recall my projects are MVC .net. In my controller, the data comes back like this:

{"Table":[{"Fund":"0000","Org":"0000000","AccountID_Adj":"13027", ...

"Table" seems to be the default for the data being returned:

    public class CallManagerUnmatchedController : ApiController
    {
        [Route("api/CallManagerUnmatched")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult CallManagerUnmatched()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;
            var AsOfCookie = request.Cookies["AsOfDate"].Value;
            string query = "select * from udf_Telecom_CallManagerData ('" + AsOfCookie + "',1) Where Fund = '0000'";
            string connectionString = settings.DbConnection;
            System.Data.DataSet ds = new System.Data.DataSet();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(query, conn))
                {
                    command.Connection = conn;
                    using (SqlDataAdapter sda = new SqlDataAdapter(command))
                    {
                        sda.Fill(ds);
                    }
                }
            }
            return Json(ds);
        }
    }

If I can change them from "Table" to something more uniquely identifiable, then I believe I can use your code. but not sure how to assign the "name" that the data is being returned with to other than "Table".

kthorngren

The thread I linked to checks the table ID in the search plugin. Here is an example I created for another thread:
http://live.datatables.net/tihuyiqi/1/edit

Kevin

montoyam

ahhh, nice. I will give that a try. thanks.

colin

We're about to release another extension fairly soon (probably later this month), that will have that 'not' search built-in - it's called SearchBuilder. It's not released yet, we're still tweaking it, but this here will give you a good idea of what it can do.

Colin

tangerine

@Colin - great idea. Now, if only it were applicable to individual column filters, joy would be unconfined...

montoyam

Ok. I am almost there. However, I realize that the array.includes() function as well as .filter() and .some() are looking for complete values. I am needing to see where a value of ANY field is LIKE something. I guess for now I will hard code the field names and use string.includes() which seems to do partial text matching

kthorngren

You should be able to loop through the searchData parameter. Its an array of the column data for the row.

Kevin

montoyam

this seems to have done the trick:

        /* Custom filtering function which will search all columns and hide record when data contains search word*/
        $.fn.dataTable.ext.search.push(
            function (settings, data, dataIndex) {
                if (settings.nTable.id !== 'CallManagerNoType') {
                    return true;
                } else {
                    var notLike = $('#notIn_NoType').val();
                    if (notLike == undefined || notLike == '') {
                        return true;
                    } else {
                        var result = !(data.toString().toLowerCase().includes(notLike.toLowerCase()));
                        console.log(notLike, result, data);
                    }
                    return result;
                }
            }
        );  

                    CallManagerNoTypeDataTable = $('#CallManagerNoType').DataTable({
                        dom: '<"NoTypeNotInSearch">frtip',
                        destroy: true,
                        data: data.Table,
                        rowId: 'CM_ID',
                        scrollX: true,
                        columns: columns
                    })

                    $("div.NoTypeNotInSearch").html('<div>Hide:<input type="text" id="notIn_NoType" name="notIn_NoType" /></div>');
                    // Event listener to 'not in' filtering inputs to redraw on input
                    $('#notIn_NoType').keyup(function () {
                        CallManagerNoTypeDataTable.draw();
                    });

montoyam

i further refined it so the user can enter multiple words separated by a space, just like the built in search box.

        $.fn.dataTable.ext.search.push(
            function (settings, data, dataIndex) {
                if (settings.nTable.id !== 'CallManagerNoType') {
                    return true;
                } else {
                    var notLike = $('#notIn_NoType').val();
                    if (notLike == undefined || notLike == '') {
                        return true;
                    } else {
                        var hideItems = notLike.split(" ");
                        var result = true;
                        for (index = 0; index < hideItems.length; index++) {
                            if (hideItems[index]) {
                                (data.toString().toLowerCase().includes(hideItems[index])) ? result = false : '';
                            }
                        } 
                        return result;
                    }
                }
            }
        );  

kthorngren

Good job, it looks good!

Kevin

montoyam

and just one more change to make it easier to add other dataTables on the same page:

        /* Custom filtering function which will search all columns and hide record when data contains search word*/
        $.fn.dataTable.ext.search.push(
            function (settings, data, dataIndex) {
                var notLike = '';
                switch (settings.nTable.id ) {
                    case 'CallManagerNoType':
                        notLike = $('#notIn_NoType').val();
                        break;
                    case 'CallManagerUnmatched':
                        notLike = $('#notIn_Unmatched').val();
                        break;
                    //add other tables as needed
                }
                if (notLike == undefined || notLike == '') {
                    return true;
                } else {
                    var hideItems = notLike.split(" "); //user can hide multiple items, separated by a space
                    var result = true;
                    for (index = 0; index < hideItems.length; index++) {
                        if (hideItems[index]) {
                            (data.toString().toLowerCase().includes(hideItems[index])) ? result = false : ''; //don't show record if search word is found in data
                        }
                    }
                    return result;
                }

            }
        );