Filter with "OR" And "AND" Conditions on Multiple Fields

I want to implement the below where condition. How do I create the filter in UI5?

field-A NE 'O' and ( field-B contains 'search-text' or field-C contains 'search-text' )

The backend business scenario:

  1. Apply the filter field-A NE 'O' when binding list.
  2. Apply the filter ( field-B contains 'search-text' or field-C contains 'search-text' ) to implement the search function on the search field.

Filter instances:

new sap.ui.model.Filter("field-A", sap.ui.model.FilterOperator.NE, "O");
new sap.ui.model.Filter("field-B", sap.ui.model.FilterOperator.contains, search-text);
new sap.ui.model.Filter("field-C", sap.ui.model.FilterOperator.contains, search-text);

Here is a minimal example of combining multiple filters using OData from Northwind: https://embed.plnkr.co/AoIZI4/. The complete list can be found here.

When instantiating a filter, instead of path, operator, and value1, use the properties filters and and to combine multiple filters as shown in the Filter API reference.

In our case, we define three filters:

  • One for the first field-A NE 'O' which is also used on the initial binding in the Plunker example above (Filter 1)
  • And for the other two in the search event handler with and: false meaning OR (Filter 2).

Filter 1:

getInitialFilter: function() {
  return new Filter("Field-A", FilterOperator.NE, "O");
},

Filter 2:

getSearchFilters: function(query) {
  return new Filter({
    filters: [
      new Filter("Field-B", FilterOperator.Contains, query),
      new Filter("Field-C", FilterOperator.Contains, query),
    ],
    and: false,
  });
},

Finally, when the user enters a search query, we combine those two filters with and: true applying on the ODataListBinding.

onSearch: function(event) {
  this.byId("myList").getBinding("items").filter(new Filter({
    filters: [
      this.getInitialFilter(),
      this.getSearchFilters(event.getParameter("query")),
    ],
    and: true,
  }), FilterType.Application);
},

Note: When filtering, keep in mind to apply the FilterType "Application" as the 2nd argument in myListBinding.filter to let the framework know that the filter was set by you (application) and not by a control. Otherwise, the list binding will combine your filters with the application filters which were initially set.


var andFilter = [];

var orFilter = [];

orFilter.push(new sap.ui.model.Filter("title", sap.ui.model.FilterOperator.Equal, "filtervalue"));

orFilter.push(new sap.ui.model.Filter("status", sap.ui.model.FilterOperator.Equal, "filtervalue"));

andFilter.push(new sap.ui.model.Filter(orFilter, false));

orFilter = [];

orFilter.push(new sap.ui.model.Filter("title", sap.ui.model.FilterOperator.Equal, "filtervalue1"));

orFilter.push(new sap.ui.model.Filter("status", sap.ui.model.FilterOperator.Equal, "filtervalue1"));

andFilter.push(new sap.ui.model.Filter(orFilter, false));

oBinding.filter(new sap.ui.model.Filter(andFilter, true));

That should translate to:

title=filtervalue || status=filtervalue && title=filtervalue1 || status=filtervalue1

Use below syntax for multiple filter with OR condition: (change false to true for AND)

var InputFilter = new sap.ui.model.Filter({
  filters: [
    new sap.ui.model.Filter("Name", sap.ui.model.FilterOperator.EQ, inptval),
    new sap.ui.model.Filter("ID", sap.ui.model.FilterOperator.EQ, inptval)
  ],
  and: false
});

this.oModel.read("/Products", {
  filters: [InputFilter],
  success: jQuery.proxy(this._fnSuccessGet, this),
  error: jQuery.proxy(this._fnErrorGet, this)
});