Skip to content

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:

backendlessdatafiltering.zoom90

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:

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:

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:

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 ?? "")")
})