Skip to content

Inverted Relation Retrieval

Backendless supports a special query syntax for loading a subset of child objects for a specific parent. Consider the following table schemas:

PhoneBook table:

phonebook-table-schema

Contact table:

contact-table-schema

Address table:

address-table-schema

These tables will be used to demonstrate how to query Backendless for conditional related object retrieval.

Address Class:

@interface Address : NSObject

@property (strong, nonatomic) NSString *state;
@property (strong, nonatomic) NSString *city;
@property (strong, nonatomic) NSString *street;

@end
@objcMembers class Address: NSObject {
    var state: String?
    var city: String?
    var street: String?
}

Contact Class:

@interface Contact : NSObject

@property (strong, nonatomic) NSString *name;
@property (strong, nonatomic) NSNumber *age;
@property (strong, nonatomic) NSString *phone;
@property (strong, nonatomic) NSString *title;
@property (strong, nonatomic) Address *address;

@end
@objcMembers class Contact: NSObject {
    var name: String?
    var age: NSNumber?
    var phone: String?
    var title: String?
    var address: Address?
}

PhoneBook Class:

@interface PhoneBook : NSObject

@property (strong, nonatomic) NSString *objectId;
@property (strong, nonatomic) Contact *owner;
@property (strong, nonatomic) NSArray<Contact *> *contacts;

@end
@objcMembers class PhoneBook: NSObject {    
    var objectId: String?
    var owner: Contact?
    var contacts: [Contact]?
}

The dictionary-based approach does not require custom classes.

The general structure of a whereClause query to load a collection of child objects for a specific parent object is:

ParentTableName[ relatedColumnName ].parentColumnName COLUMN-VALUE-CONDITION

Both columns relatedColumnName and parentColumnName must be declared in a table with name of ParentTableName. The relatedColumnName must be a relation column. The table relatedColumnName points to is the table where the objects must be loaded from.  The examples below demonstrate the usage of this syntax:

Use the following code for all examples listed below. The only difference in the scenarios is the condition in whereClause.

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.whereClause = @"PUT THE WHERE CLAUSE STATEMENT HERE";

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 in a city for a specific phone book:
NSString *whereClause = [NSString stringWithFormat:@"PhoneBook[contacts].objectId = '%@' and address.city = 'Smallville'", savedPhoneBook.objectId];
Find all contacts for the specific phone book where the city name contains letter 'a':
NSString *whereClause = [NSString stringWithFormat:@"PhoneBook[contacts].objectId = '%@' and address.city like '%a%'", savedPhoneBook.objectId];
Find all contacts where age is greater than 20 for a specific phone book:
NSString *whereClause = [NSString stringWithFormat:@"PhoneBook[contacts].objectId = '%@' and age > 20", savedPhoneBook.objectId];
Find all contacts for a specific phone book where age is within the specified range:
NSString *whereClause = [NSString stringWithFormat:@"PhoneBook[contacts].objectId = '%@' and age >= 21 and age <= 30", savedPhoneBook.objectId];
Find all contacts for a specific phone book where age is greater than 20 and the city is Tokyo:
NSString *whereClause = [NSString stringWithFormat:@"PhoneBook[contacts].objectId = '%@' and age > 20 and address.city = 'Tokyo'", savedPhoneBook.objectId];

Use the following code for all examples listed below. The only difference in the scenarios is the condition in whereClause.

let queryBuilder = DataQueryBuilder()
queryBuilder.whereClause = "PUT THE WHERE CLAUSE STATEMENT HERE"

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 in a city for a specific phone book:
let whereClause = "PhoneBook[contacts].objectId = '\(savedPhoneBook.objectId!)' and address.city = 'Smallville'"
Find all contacts for the specific phone book where the city name contains letter 'a':
let whereClause = "PhoneBook[contacts].objectId = '\(savedPhoneBook.objectId!)' and address.city like '%a%'"
Find all contacts where age is greater than 20 for a specific phone book:
let whereClause = "PhoneBook[contacts].objectId = '\(savedPhoneBook.objectId!)' and age > 20"
Find all contacts for a specific phone book where age is within the specified range:
let whereClause = "PhoneBook[contacts].objectId = '\(savedPhoneBook.objectId!)' and age >= 21 and age <= 30"
Find all contacts for a specific phone book where age is greater than 20 and the city is Tokyo:
let whereClause = "PhoneBook[contacts].objectId = '\(savedPhoneBook.objectId!)' and age > 20 and address.city = 'Tokyo'"

Use the following code for all examples listed below. The only difference in the scenarios is the condition in whereClause.

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.whereClause = @"PUT THE WHERE CLAUSE STATEMENT HERE";

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 in a city for a specific phone book:
"PhoneBook[contacts].objectId = '%@' and address.city = 'Smallville'"
Find all contacts for the specific phone book where the city name contains letter 'a':
"PhoneBook[contacts].objectId = '%@' and address.city like '%%a%%'"
Find all contacts where age is greater than 20 for a specific phone book:
"PhoneBook[contacts].objectId = '%@' and age > 20"
Find all contacts for a specific phone book where age is within the specified range:
"PhoneBook[contacts].objectId = '%@' and age >= 21 and age <= 30"
Find all contacts for a specific phone book where age is greater than 20 and the city is Tokyo:
"PhoneBook[contacts].objectId = '%@' and age > 20 and address.city = 'Tokyo'"

Use the following code for all examples listed below. The only difference in the scenarios is the condition in whereClause.

let queryBuilder = DataQueryBuilder()
queryBuilder.whereClause = "PUT THE WHERE CLAUSE STATEMENT HERE"

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 in a city for a specific phone book:
let whereClause = "PhoneBook[contacts].objectId = '\(savedPhoneBook["objectId"]!)' and address.city = 'Smallville'"
Find all contacts for the specific phone book where the city name contains letter 'a':
let whereClause = "PhoneBook[contacts].objectId = '\(savedPhoneBook["objectId"]!)' and address.city like '%a%'"
Find all contacts where age is greater than 20 for a specific phone book:
let whereClause = "PhoneBook[contacts].objectId = '\(savedPhoneBook["objectId"]!)' and age > 20"
Find all contacts for a specific phone book where age is within the specified range:
let whereClause = "PhoneBook[contacts].objectId = '\(savedPhoneBook["objectId"]!)' and age >= 21 and age <= 30"
Find all contacts for a specific phone book where age is greater than 20 and the city is Tokyo:
let whereClause = "PhoneBook[contacts].objectId = '\(savedPhoneBook["objectId"]!)' and age > 20 and address.city = 'Tokyo'"

Codeless Reference

The following example is identical to the one presented above and uses the following condition in thewhere clause property: This operation is set to retrieve related objects using the age properties whose values is greater than 21, and also the city properties whose value is 'Tokyo'.

PhoneBook[contacts].objectId = 'DB7A04FD-C305-4615-8480-BF75F244F10F' and age > 21 and address.city = 'Tokyo'

data_inverted_relation_retrieval

After the Codeless logic runs, the operation returns only one object matching the where clause condition.

data_inverted_relation_retrieval_6