Data Filtering

Filtering data in database queries provides a way to narrow the set of objects which should be returned by the server. When using the aggregate functions with grouping, there are two filtering steps. The first one is by using the whereClause condition which identifies the initial set of data objects. If there is a grouping function in a query, the groups are created from that set. It is possible to apply another condition for the grouping function using the having clause. The diagram below illustrates the process:

The example below retrieves an average box office earnings for the movies which grossed more than $2 billion released after 2012 grouped by the release year. Notice the having clause which sets the condition for the groups:

Objective-CSwift

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.properties = @[@"Avg(totalBoxOffice)"];
queryBuilder.groupBy = @[@"yearReleased"];
queryBuilder.havingClause = @"Avg(totalBoxOffice) > 2000000000";

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Movie"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundObjects) {
    NSLog(@"%@", foundObjects);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];

let queryBuilder = DataQueryBuilder()
queryBuilder.properties = ["Avg(totalBoxOffice)"]
queryBuilder.groupBy = ["yearReleased"]
queryBuilder.havingClause = "Avg(totalBoxOffice) > 2000000000"

let dataStore = Backendless.shared.data.ofTable("Movie")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundObjects in
    print("\(foundObjects)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

Alternatively, the having clause can reference an aggregate function by the assigned alias:

Objective-CSwift

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.properties = @[@"Avg(totalBoxOffice) as averageAmount"];
queryBuilder.groupBy = @[@"yearReleased"];
queryBuilder.havingClause = @"averageAmount > 2000000000";

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Movie"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundObjects) {
    NSLog(@"%@", foundObjects);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];

let queryBuilder = DataQueryBuilder()
queryBuilder.properties = ["Avg(totalBoxOffice) as averageAmount"]
queryBuilder.groupBy = ["yearReleased"]
queryBuilder.havingClause = "averageAmount > 2000000000"

let dataStore = Backendless.shared.data.ofTable("Movie")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundObjects in
    print("\(foundObjects)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

Important

Both having and groupBy can use property functions to apply the having and grouping condition on a value calculated by an expression.

The having clause operates only on the group properties. These properties may include information about the related objects as well. For example, the request below counts the number of actors for the movies grouped by the release year and filters out only those where there are more than 3 actors:

Objective-CSwift

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.properties = @[@"Count(actors) as actorCount"];
queryBuilder.groupBy = @[@"yearReleased"];
queryBuilder.havingClause = @"actorCount > 3";

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Movie"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundObjects) {
    NSLog(@"%@", foundObjects);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];

let queryBuilder = DataQueryBuilder()
queryBuilder.properties = ["Count(actors) as actorCount"]
queryBuilder.groupBy = ["yearReleased"]
queryBuilder.havingClause = "actorCount > 3"

let dataStore = Backendless.shared.data.ofTable("Movie")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundObjects in
    print("\(foundObjects)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

Codeless Reference

Consider the following data table called Movie:

Important

All examples presented further reflect the operation logic and behavior as described in the beginning of this topic.

The example below retrieves an average box office value for movies released after 2012 with earnings exceeding $500 million using the where and having clauses.

Important

For a detailed description of all input parameters see the Basic Object Retrieval topic of this guide.

After the Codeless Logic runs, the operation returns two objects containing a custom averageBoxOffice property and the corresponding value representing an average box office earnings. Only two objects were returned since the condition for the where clause was set to return all box office earnings for movies released after 2012.

The next operation is identical to the previous one, but it shows how an alias (averageBoxOffice) can be used in the request instead of the aggregate function (Avg(totalBoxOffice)).

You can also include the related information to the query. For demonstration purpose, the Movie data table has a column actors which has a relation to another data table. The operation below is set to count the number of actors for each movie and include only those with 3 or more actors.