Skip to content

Search with the Where Clause

A where clause is a condition which references columns of a data table where the query is sent. For any given query Backendless returns the data records which match the condition in the where clause. A where clause is a query string which must conform to a subset of the SQL-92 standard. The subset of the syntax supported by Backendless is the part that goes into the WHERE clause of an SQL query. This is the reason why we refer to it as the where clause. For example, consider the following data objects, specifically notice the columns name and age:

sample-table.zoom70

Values for columns of types STRING and TEXT must be wrapped in single quotes. Suppose you need to retrieve an object where the value of the name property is "Joe". This can be accomplished with the following where clause:

name = 'Joe'

If the value itself has a single quote, it can be "escaped" by adding an additional single quote. For example:

name = 'Charlie''s Angels'

If you need all objects where the age column contains a value less than 30:

age < 30

Just like in SQL, columns can be combined in a single where clause. The following condition would retrieve all objects containing letter J in the name and the age column's value is greater than 20:

name LIKE 'J%' and age > 20
Condition
Sample where clause
Column type
Value in a column equals a string literal.
name = 'foo'
STRING, TEXT or EXTENDED STRING
Value in a column does not equal a string literal.
name != 'foo'
STRING, TEXT or EXTENDED STRING
Value in a column is not assigned
name is null
STRING, TEXT or EXTENDED STRING
Value in a column is assigned
name is not null
STRING, TEXT or EXTENDED STRING
Value in a column contains a substring literal
name LIKE '%foo%'
STRING, TEXT or EXTENDED STRING
Value in a column ends with a substring literal
name LIKE '%foo'
STRING, TEXT or EXTENDED STRING
Value in a column starts with a substring literal
name LIKE 'foo%'
STRING, TEXT or EXTENDED STRING
Value in a column is one of enumerated string literals.
name IN ('value1', 'value2', 'value3)

or

name = 'value1' OR name = 'value2' or name = 'value3'
STRING, TEXT or EXTENDED STRING
Value in a column equals a numeric literal
age = 21
INT or DOUBLE
Value in a column is greater than a numeric literal
age > 21
INT or DOUBLE
Value in a column is greater than or equal a numeric literal
age >= 21
INT or DOUBLE
Value in a column is less than a numeric literal
age < 21
INT or DOUBLE
Value in a column is less than or equal a numeric literal
age <= 21
INT or DOUBLE

Find all contacts where the value of the "age" property equals 47:
DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setWhereClauseWithWhereClause:@"age = 47"];

DataStoreFactory *dataStore = [Backendless.shared.data of:[Contact class]];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundContacts) {
    NSLog(@"Found contacts: %@", foundContacts);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
Find all contacts where the value of the "age" property is greater than 21:
DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setWhereClauseWithWhereClause:@"age > 21"];

DataStoreFactory *dataStore = [Backendless.shared.data of:[Contact class]];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundContacts) {
    NSLog(@"Found contacts: %@", foundContacts);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
Find all contacts by name:
DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setWhereClauseWithWhereClause:@"name = 'Jack Daniels'"];

DataStoreFactory *dataStore = [Backendless.shared.data of:[Contact class]];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundContacts) {
    NSLog(@"Found contacts: %@", foundContacts);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
Find all contacts by partial name match:
DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setWhereClauseWithWhereClause:@"name LIKE 'Jack%'"];

DataStoreFactory *dataStore = [Backendless.shared.data of:[Contact class]];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundContacts) {
    NSLog(@"Found contacts: %@", foundContacts);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
Find all contacts where the value of the "age" property equals 47:
let queryBuilder = DataQueryBuilder()
queryBuilder.setWhereClause(whereClause: "age = 47")

let dataStore = Backendless.shared.data.of(Contact.self)
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundContacts in
    print("Found contacts: \(foundContacts)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})
Find all contacts where the value of the "age" property is greater than 21:
let queryBuilder = DataQueryBuilder()
queryBuilder.setWhereClause(whereClause: "age > 21")

let dataStore = Backendless.shared.data.of(Contact.self)
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundContacts in
    print("Found contacts: \(foundContacts)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})
Find all contacts by name:
let queryBuilder = DataQueryBuilder()
queryBuilder.setWhereClause(whereClause: "name = 'Jack Daniels'")

let dataStore = Backendless.shared.data.of(Contact.self)
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundContacts in
    print("Found contacts: \(foundContacts)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})
Find all contacts by partial name match:
let queryBuilder = DataQueryBuilder()
queryBuilder.setWhereClause(whereClause: "name LIKE 'Jack%'")

let dataStore = Backendless.shared.data.of(Contact.self)
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundContacts in
    print("Found contacts: \(foundContacts)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

Find all contacts where the value of the "age" property equals 47:
DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setWhereClauseWithWhereClause:@"age = 47"];

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Contact"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundContacts) {
    NSLog(@"Found contacts: %@", foundContacts);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
Find all contacts where the value of the "age" property is greater than 21:
DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setWhereClauseWithWhereClause:@"age > 21"];

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Contact"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundContacts) {
    NSLog(@"Found contacts: %@", foundContacts);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
Find all contacts by name:
DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setWhereClauseWithWhereClause:@"name = 'Jack Daniels'"];

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Contact"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundContacts) {
    NSLog(@"Found contacts: %@", foundContacts);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
Find all contacts by partial name match:
DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setWhereClauseWithWhereClause:@"name LIKE 'Jack%'"];

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Contact"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundContacts) {
    NSLog(@"Found contacts: %@", foundContacts);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
Find all contacts where the value of the "age" property equals 47:
let queryBuilder = DataQueryBuilder()
queryBuilder.setWhereClause(whereClause: "age = 47")

let dataStore = Backendless.shared.data.ofTable("Contact")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundContacts in
    print("Found contacts: \(foundContacts)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})
Find all contacts where the value of the "age" property is greater than 21:
let queryBuilder = DataQueryBuilder()
queryBuilder.setWhereClause(whereClause: "age > 21")

let dataStore = Backendless.shared.data.ofTable("Contact")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundContacts in
    print("Found contacts: \(foundContacts)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})
Find all contacts by name:
let queryBuilder = DataQueryBuilder()
queryBuilder.setWhereClause(whereClause: "name = 'Jack Daniels'")

let dataStore = Backendless.shared.data.ofTable("Contact")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundContacts in
    print("Found contacts: \(foundContacts)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})
Find all contacts by partial name match::
let queryBuilder = DataQueryBuilder()
queryBuilder.setWhereClause(whereClause: "name LIKE 'Jack%'")

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