Filtering

You can apply filters to Read, Update, and Delete data operations to alter the result of the query.

In this article:

Assembling a Filter Query

Filtering is implemented through the WhereQuery class. You can apply it to queries in a fluent API fashion with the where() method. It allows you to chain a number of predefined mathematical, logical, and restriction-based filter rules before ending the chain with the done() method. The where() method returns a WhereQuery object.

An example filtering query would look like this:

query.where().gt('age', 5).lt('age', 15).done();

Telerik Platform implies logical AND between the rules, unless you specify different logic through a combination of the and(), or(), and not() methods. For example, the following queries are interchangeable.

query.where().and().eq('name', 'Joe').eq('age', 25);
//is equivalent to
query.where().eq('name', 'Joe').eq('age', 25);

Calling done() is optional if there are no following Query conditions, but it is highly recommended to include it for consistency and clarity.

query.where().eq('name', 'Joe').eq('age', 25);
 //is equivalent to
query.where().eq('name', 'Joe').eq('age', 25).done();
 //but you can't skip done() here
query.where().eq('name', 'Joe').done().sort('age');

Reading by Filter

The data.get() method takes a single argument which must define your filtering criteria. Below you can find examples for constructing filter expressions using the methods listed in Usage and Methods Overview and in the Backend Services JavaScript SDK API Reference.

Value Equal To

The following example returns all items from the Books content type that have their Author field set to Ordinary Joe.

You can invert the logic, returning all items except for Ordinary Joe books, by replacing the eq() method with ne() (not equal).

var el = new Everlive('your-app-id');

var filter = {
    'Author': 'Ordinary Joe'
};
//or
var filter = new Everlive.Query();
filter.where().eq('Author', 'Ordinary Joe');

var data = el.data('Books');
data.get(filter)
    .then(function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

el.data('Books').get

The eq() function can also match a single value inside an array field. See Value Inside an Array Field for an example.

Value Greater Than

The following example returns all items from the Products content type which have more than a thousand pieces on hand.

Other comparison functions that you can use in a similar manner are lt(), gte(), and lte() that correspond to "less than", "greater than or equal to", and "less than or equal to" respectively.

var el = new Everlive('your-app-id');
var data = el.data('Products');
var query = new Everlive.Query();
query.where().gt('Inventory', 1000);
data.get(query) // filter
    .then(function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

Value in Array of Possible Matches

The following example returns all items from the Books content type that have their Author field set to Ordinary Joe or John Doe.

You can invert the logic, returning all items except for those two authors', by replacing the isin() method with notin().

var el = new Everlive('your-app-id');
var data = el.data('Books');
var query = new Everlive.Query();
query.where().isin('Author', ['Ordinary Joe', 'John Doe']);
data.get(query) // filter
    .then(function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

Compound Expressions

You can combine multiple filter criteria in an expression using the logical operators and(), or(), or not().

The following code returns all products that have high inventory in the Pittsburgh warehouse.

var el = new Everlive('your-app-id');
var data = el.data('Products');
var query = new Everlive.Query();
query
    .where()
        .and()
            .gt('Inventory', 1000)
            .eq('Location', 'Pittsburgh')
    .done();
data.get(query) // filter
    .then(function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

In the previous example, the and() function is technically redundant as it is implied. However, if you are using or() or a combination of conjunctions, disjunctions, and negations, you cannot skip the operator.

Check the code below for an example. It returns all people aged 20 to 30 with first name John or Jane:

var query = new Everlive.Query();
query.where()
  .and()
   .or() // Name is eigher "John" or "Jane"
       .eq('FirstName', 'John')
       .eq('FirstName', 'Jane')
       .done()
   .and() // Age between 20 and 30
       .gt('Age', 20)
       .lt('Age', 30)
       .done()
   .done();

Date Range Expressions

You can use the Date JavaScript object to specify start and end dates for you query. The following example shows how to dynamically specify start and end dates relative to the current date.

var el = new Everlive('your-app-id');
var data = el.data('type-name');
var query = new Everlive.Query();

var rangeStart = new Date();
rangeStart.setDate(rangeStart.getDate() - 10); // 10 days before today
var rangeEnd = new Date();
rangeEnd.setDate(rangeEnd.getDate() + 10); // 10 days after today

query
    .where()
        .and()
            .gte('CreatedAt', rangeStart) // all items created after (>=) the rangeStart date
            .lte('CreatedAt', rangeEnd) // all items created before (<=) the rangeEnd date
        .done();

data.get(query) // filter
    .then(function(data) {
            alert(JSON.stringify(data));
        },
        function(error) {
            alert(JSON.stringify(error));
        });

Regular Expressions

You can filter values that match a regex pattern. Telerik Platform understands Perl-compatible regular expressions (PCRE), version 8.39, with UTF-8 support.

Important

Regular expressions support when filtering data from Data Connectors is limited to the following use cases. As a workaround, create a view or a stored procedure and map it to a Telerik Platform content type or cloud function respectively.

  • Starts with: ^searchstring (Example: var filter = {"name":{"$regex":"^John"}};)
  • Ends with: searchstring$ (Example: var filter = {"name":{"$regex":"John$"}};)
  • Contains: .*searchstring.* (Example: var filter = {"name":{"$regex":".*John.*"}};)

You can specify regular expression options using the third argument of the whereQuery.regex() function.

Important

Specifying regular expression options is not supported when filtering data from Data Connectors. Do not specify any, otherwise you will receive an incorrect result. In terms of case sensitivity, Telerik Platform always preserves the case in the request, but the type of search (case-sensitive or case-insensitive) that your data store will perform depends on its settings.

The following example returns all authors whose first name begins with the letter M. The i option requests case insensitivity, which means that the regex will match author names beginning both with lowercase and uppercase M.

var el = new Everlive('your-app-id');

var data = el.data('Books');
var query = new Everlive.Query();

var startsWith = "^M";
// var endsWith = "v$";

query.where().regex('Author', startsWith, "i");
data.get(query) // filter
    .then(function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

Value Inside an Array Field

The eq() WhereQuery function can also match a single value inside an array-type field but only when the array contains primitive data types: number, string, boolean, or date.

The following example will return only items whose Zip field contains the 15212 ZIP code, even if there are other values inside the array.

//"Zip":["11413","15212","32003"]

var query = new Everlive.Query();
query.where().eq("Zip", "15212").done();

Updating by Filter

To update all items that match your filtering criteria, pass a Query class object as the second argument to the data.update() method. It is very important to test the criteria beforehand because the update operation is potentially destructive with the wrong selection.

The next code defines a filter that selects all books except for those written by Ordinary Joe and then updates their publisher.

var el = new Everlive('your-app-id');
var data = el.data('Books');
var query = new Everlive.Query();
query.where().ne('Author', 'Ordinary Joe');
data.update({ 'Publisher': 'Mars Publishing' }, // data
    query, // filter
    function(data){
        alert(JSON.stringify(data));
    },
    function(error){
        alert(JSON.stringify(error));
    });

Deleting by Filter

You are advised to use deletion based on filtering criteria with extreme caution. If, due to a mistake, your filter ends up matching a wider range of data than you expected, you may lose data irreversibly.

The following code deletes all books written by Ordinary Joe.

var el = new Everlive('your-app-id');
var data = el.data('Books');
var query = new Everlive.Query();
query.where().eq('Author', 'Ordinary Joe');
data.destroy(query, // filter
    function (data) {
        alert('Items successfully deleted.');
    },
    function(error){
        alert(JSON.stringify(error));
    });

See Also

Start a free trial Request a demo
Contact us: +1-888-365-2779
sales@telerik.com
Copyright © 2016-2017, Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.