LIKE SQL Before and after word

#1

Hello,
is it possible to insert SQL Like before and after word?
For example:
We want to select the address “Via Giorgio Ribotta,30” from the table “PLACES”.

SELECT ADDRESS FROM PLACES WHERE ADDRESS LIKE ‘%via%Ribotta%’;

the result of this query it’s "Via Giorgio Ribotta,30 ".

I would use this features on UIViewFieldSelectField component.
Thanks for your support

#2

Here is an example of UIViewFieldSelectField that and how to use the contains comparator

                    {
                        "label": "{~EnergyManagement.FacilitiesFilter.Organization.text~}",
                        "component": "UIViewControlGroup",
                        "comparator": "contains",
                        "options": {
                            "toUpperCase": {"value": true}
                        },
                        "field": {
                            "component": "UIViewFieldSelectField",
                            "id": "rootOrg",
                            "name": "ownerOrgs",
                            "data": {
                                "collection": "EnergyManagement.FacilitiesDepartments"
                            },
                            "displayField": "name",
                            "valueField": "id",
                            "autoSelectFirst": false,
                            "emptyOption": true,
                            "ignorePageRecord": true
                        }
                    }
#3

Hi Bachir, thank you for answer, but this not works; because I have this string
BMS System_Tag stopped.
I would write ‘BMS stop’ to find full value

#4

There is the matchesRegex ExpressionEngineFunction which does what you are looking for; unfortunately the UIViewFieldSelectField type does not natively support this matcher for fetch calls.

We actually had to implement something very similar recently. We had a UIViewFieldText field inside of a UIViewFilterForm component to filter a list. We needed one of these fields to support “wildcard” syntax, so we ended up remixing the UIViewFieldText.generateFetchSpec method.

Specifically, we changed this part:

 _.each(this.selfAndChildren(), function(cmp) {
    if (UIUtil.isInstanceOf(cmp, UIViewControlGroup)) {
      var currentFilter = cmp.getFilterConfig(),
        value = currentFilter.get('value'),
        currentFilterValue = (value == null || value === "") ? currentFilter.get('values') : value, //zero should be included
        emptyFilter = !C3.util.notEmpty(currentFilterValue) || currentFilterValue === 'null';

      if (!emptyFilter || currentFilter.get('comparator') === "NULL") {
        filters.push(currentFilter);
      }
    }
  });

to this:

  _.each(this.selfAndChildren(), function(cmp) {
    if (UIUtil.isInstanceOf(cmp, UIViewControlGroup)) {
      var currentFilter = cmp.getFilterConfig();
      
      // If currentFilter id == tagNameField, then insert regex filter
      if (currentFilter.get('id') === tagNameField) {
        var currentValue = currentFilter.get('value');
        if (currentValue && (currentValue.indexOf('*') > -1 || currentValue.indexOf('?') > -1)) {
          currentFilter.set('comparator', 'matchesRegex');
          currentFilter.set('value', UIHelper.wildcardToRegex(currentValue, true));
        }
      }

      var value = currentFilter.get('value');
      var currentFilterValue = (value == null || value === '') ? currentFilter.get('values') : value; //zero should be included
      var emptyFilter = !C3.util.notEmpty(currentFilterValue) || currentFilterValue === 'null';

      if (!emptyFilter || currentFilter.get('comparator') === 'NULL') {
        filters.push(currentFilter);
      }
    }
  });

note the currentFilter.set('comparator', 'matchesRegex'); line - this means the fetch call ends up using matchesRegex instead of contains for its filter on the query

#5

Hi Scott,
I have looked inside UIViewFieldText, but the function generateFetchSpec() doesn’t exists.
Maybe I have to update something or switch branch?
I’m working on develop-78

#6

Are you using this inside of a UIViewFilterForm component? That is where we updated the generateFetchSpec method.

#7

if I write UIViewFieldText.generateFetchSpec() in a c3 console its says that the function doesn’t exists. I try to use inside the component but is the same

#8

That’s because it’s UIViewFilterForm.generateFetchSpec() - assuming you are using the UIViewFilterForm component to filter a list, then clicking the “Filter” button at the bottom of a filter form triggers this function. If that’s not the case, please provide more detail on how you plan to implement this filter functionality.

#9

I don’t want to do this type of search when I click on filter button, but when I start to digit inside the component. I show you in a screenshot

As you can see if I digit in the box BMS (img 1) the filter works; if I digit in the box BMS Tag (img 2), that aren’t consecutive words, the filter returns “no results”.

1 2

#10

That looks like you are using the UIViewFieldSelectField component - in that case you can remix this component (or create a new component which mixes UIViewFieldSelectField) and modify the logic in the render() method.

Note this block:

inputField.select2({
      placeholder: this.get('placeholder'),
      multiple: this.get('multiple'),
      allowClear: this.get('allowClear'),
      dropdownAutoWidth: this.get('dropdownAutoWidth'),
      minimumResultsForSearch: this.get('minimumResultsForSearch'),
      tokenSeparators: tokenSeparators,
      formatResult: self.generateRowTemplate.bind(self),
      formatSelection: self.generateRowTemplate.bind(self),
      separator: this.get('separator'),
      createSearchChoice: tokenSeparators.length > 0 ? function(term, data) {
        if ($(data).filter(function() {
            return this.text.localeCompare(term)===0;
          }).length===0) {
          return {id:term, text:term};
        }
      } : null,
      query: function(query) {
        self.retrieveData(query.term, true, query.callback, query.page);
        if (self.get("multipleRow")) {
          self.positionDropdown(self.jquery("input"));
        }
      },
      initSelection: function(element, callback) {
        var data = {id: element.val(), text: element.val()};
        callback(data);
      }
    });

you can modify the query function in this object to implement whatever search logic you would like. Underneath the covers, this component is using select2 - here is a link to the docs so you can see how the query field method be implemented

#11

Hi Scott, thank you for your help, I have resolved overriding a filterFunction(). Now all components of type UIViewFieldSelectField haves same behaviour