slickgridangular-slickgrid

Angular Slickgrid - Filter


"How can I add a new operator in the selection of a Compound Filter, or is it possible to set a custom filter to work like the SQL LIKE Operator?

Example: I have a column with the following data: Title 100.001 Tile 100.002 Title 11.001

If I write in the filter %Ti%001%, I want it to return rows that start with 'Ti' and end with '001'."

"angular-slickgrid": "4.2.6"


Solution

  • You can provide a different list of compound operators via Compound Operator List (custom list) but that was only introduced in Slickgrid-Universal v2.6.4 so it requires at least Angular-Slickgrid v5.6.4. The custom list would also require valid OperatorType, so adding custom operators simply won't be possible and adding a new operator is probably not useful with your use case anyway (that probably most won't help)

    So anyway, I took another look at your problem and now understand that you want to filter against a local JSON dataset (not using a backend service). However, there's currently no way to provide a custom filter predicate at the moment and so I created this new PR to add this missing feature but that will only be available with Angular-Slickgrid 8.0 and higher (this will also only work with a local JSON dataset, it will not support backend services, at least not yet). I also think that you should use a regular text filter and not a compound filter, there's no need to change the operator, we just need to change the filter predicate.

    With the next version (to be release next week to align with the upcoming Angular 18), you will be able to define a filterPredicate inside the column definition filter property as shown below

    initializeGrid() {
      this.columnDefinitions = [
        {
          id: 'title', name: 'Title', field: 'title', sortable: true, type: FieldType.string, filterable: true, 
          filter: {
            model: Filters.inputText,
            // you can use your own custom filter predicate when built-in filters aren't working for you
            filterPredicate: (dataContext, searchFilterArgs) => {
              const searchVals = (searchFilterArgs.parsedSearchTerms || []) as SearchTerm[];
              if (searchVals?.length) {
                const columnId = searchFilterArgs.columnId;
                const searchVal = searchVals[0] as string;
                const likeMatches = searchVal.split('%');
                if (likeMatches.length > 3) {
                  // for matches like "%Ta%10%" will return text that starts with "Ta" and ends with "10" (e.g. "Task 10", "Task 110", "Task 210")
                  const [_, start, end] = likeMatches;
                  return dataContext[columnId].startsWith(start) && dataContext[columnId].endsWith(end);
                } else if (likeMatches.length > 2) {
                  // for matches like "%Ta%10" will return text that starts with "Ta" and contains "10" (e.g. "Task 10", "Task 100", "Task 101")
                  const [_, start, contain] = likeMatches;
                  return dataContext[columnId].startsWith(start) && dataContext[columnId].includes(contain);
                }
                // for anything else we'll simply expect a Contains
                return dataContext[columnId].includes(searchVal);
              }
              // if we fall here then the value is not filtered out
              return true;
            } as any, // YOU WILL HAVE TO CAST IT TO `any` FOR OLDER VERSION since it doesn't currently exists
          },
        }
      ];
    }
    

    which gives the result shown below, note that the predicate is really crude and will probably need some improvements but it does the job to show how this will work future versions.

    enter image description here

    What if you are not ready to upgrade to Angular-Slickgrid 8.0?

    In that case, with a bit of extra work, you could change the default filter used by the DataView. I don't recommend doing this (the recommendation is of course to upgrade whenever possible) but in your case that would be the only workable (temporary) solution. What we'll do is to get the current DataView filter predicate (assigned internally by Slickgrid-Universal) and patch it (for the version that you are currently using, you can copy the customLocalFilter() function and its code from Slickgrid-Universal v1.4 (for Angular-Slickgrid 4.x) and then apply the patch in your local code, something like below (I tested it and it works).

    Note, below is a patched version of customLocalFilter() version, I only added the if condition as the updated patched code (uppercase console log below). Also note that I completely removed the code associated to Tree Data since I assume you are using a regular grid without a tree (but if you do use a Tree Data then add the missing code).

    NOTE the code below was written and tested in Slickgrid-Universal, you will need to replace the this.sgb.dataView with Angular-Slickgrid related code something like this.angularGrid.dataView and use angularGridReady function as well.

    patchDataViewFilter() {
      /** Local Grid Filter search */
      const filterServiceInstance = this.sgb.filterService;
      const customLocalFilter = (item: any, args: { columnFilters: ColumnFilters; dataView: SlickDataView; grid: SlickGrid; }) => {
        const grid = args?.grid;
        const columnFilters = args?.columnFilters ?? {};
    
        if (typeof columnFilters === 'object') {
          for (const columnId of Object.keys(columnFilters)) {
            const searchColFilter = columnFilters[columnId] as SearchColumnFilter;
            const columnFilterDef = searchColFilter.columnDef?.filter;
    
            // WE ARE ADDING A NEW "IF" CONDITION (the previous code only had the content of the "else")
            // user could provide a custom filter predicate on the column definition
            if (typeof columnFilterDef?.filterPredicate === 'function') {
              console.log('our filter predicate is reached!!!');
              const fpResult = columnFilterDef.filterPredicate(item, searchColFilter);
              if (!fpResult) {
                return false; // only return on false, when row is filtered out
              }
            } else {
              // THE "else" CODE CAME FROM THE ORIGINAL CODE
              const conditionOptions = filterServiceInstance.preProcessFilterConditionOnDataContext(item, searchColFilter, grid);
    
              if (typeof conditionOptions === 'boolean') {
                return conditionOptions;
              }
    
              let parsedSearchTerms = searchColFilter?.parsedSearchTerms; // parsed term could be a single value or an array of values
    
              // in the rare case that it's empty (it can happen when creating an external grid global search)
              // then get the parsed terms, once it's filled it typically won't ask for it anymore
              if (parsedSearchTerms === undefined) {
                parsedSearchTerms = getParsedSearchTermsByFieldType(searchColFilter.searchTerms, searchColFilter.columnDef.type || FieldType.string); // parsed term could be a single value or an array of values
                if (parsedSearchTerms !== undefined) {
                  searchColFilter.parsedSearchTerms = parsedSearchTerms;
                }
              }
    
              // execute the filtering conditions check (all cell values vs search term(s))
              if (!FilterConditions.executeFilterConditionTest(conditionOptions as FilterConditionOption, parsedSearchTerms)) {
                return false;
              }
            }
          }
        }
    
        // if it reaches here, that means the row is valid and passed all filter
        return true;
      };
    
      // override the filter predicate with ours
      this.sgb.dataView?.setFilter(customLocalFilter);
    }
    
    // then make sure to call it AFTER the grid is ready typically via `angularGridReady()`
    

    and here's the proof that it worked and we reached our patched filter version. You can find the patched code I use in the PR but I will probably remove it before merging the PR, you can look at this commit before I remove it (it's the exact same code as above).

    enter image description here

    Another patch alternative could be to use something like pnpm patch or anything similar to patch the code directly at the source.

    EDIT

    Side note, in the original question, the search value is actually wrong if we want an SQL LIKE that starts with A and ends with B, then we should use Ti%001 instead of %Ti%001%. I also made the change in my demo and also switched to using regex in this PR and I follow this pattern match which is more accurate with SQL LIKE

    const collection = ['Title 100.001', 'Title 100.002', 'Title 11.001', 'Title 1001.002'];
    const searchVal1 = '%001';    // ends with 001
    const searchVal2 = '%Ti%001'; // contains "Ti" + ends with 001
    const searchVal3 = '%Ti%';    // contains "Ti"
    const searchVal4 = 'Ti%001';  // combo starts with "Ti" + ends with 001
    const searchVal5 = 'Ti%';     // starts with "Ti"
    const searchVal6 = '%%';      // other
    const searchVal7 = '100';     // other
    

    EDIT 2

    We should only call return when the filter predicate is false, otherwise our other column filters stop working because when we call return we are breaking the for loop for (const columnId of Object.keys(columnFilters)) { and that is why the next column filters stop working. In other words, when we return false, it stops further filters because there's no point in filtering anymore when the row is already being removed from consideration (filtered out).

    image