Blog

How to Load Data Objects From the Server With an SQL Query

by Mark Piller on September 1, 2019

In other articles, we have reviewed multiple techniques for loading data objects from persistent server-side storage. There is a list of all articles categorized by topic. In this post, we are going to review the API for loading data objects using an SQL query.

We already wrote once about SQL queries in Backendless and that post described how to test queries using Backendless Console. We recommend reading that post before reviewing the samples below.

Consider the following data tables:

There are two Person objects:

And there are three Address objects. Notice there is a one-to-many relation between Person and Address objects:

Suppose you need to load all persons who are older than 21 and live in New York City. The following query expresses exactly that condition:

age > 25 and addresses.city = 'New York'

Notice the query references object properties/columns to define the search expression. The addresses column in the Person table references a collection of the Address objects. Since Address has the city column, it is identified using the dot notation in the query.

The code below demonstrates the query in action:

DataQueryBuilder dataQuery = DataQueryBuilder.create();
dataQuery.setWhereClause("age > 25 and addresses.city = 'New York'");
dataQuery.addRelated("addresses");

Backendless.Data.of(Person.class).find(dataQuery, new AsyncCallback<List<Person>>() {
   @Override
   public void handleResponse(List<Person> nycPeople) {
       for (Person person : nycPeople) {
           Log.i(TAG, "Name - " + person.name);
           Log.i(TAG, "Age - " + person.age);
           Log.i(TAG, "City - " + person.addresses.get(0).city);
       }
   }

   @Override
   public void handleFault(BackendlessFault fault) {
       Log.e(TAG, fault.getMessage());
   }
});

val dataQuery = DataQueryBuilder.create()
dataQuery.whereClause = "age > 25 and addresses.city = 'New York'"
dataQuery.addRelated("addresses")

Backendless.Data.of(Person::class.java).find(dataQuery, object : AsyncCallback<List<Person>> {
   override fun handleResponse(nycPeople: List<Person>) {
       for (person in nycPeople) {
           Log.i(TAG, "Name - ${person.name}")
           Log.i(TAG, "Age - ${person.age}")
           Log.i(TAG, "City - ${person.addresses[0].city}")
       }
   }

   override fun handleFault(fault: BackendlessFault) {
       Log.e(TAG, fault.message)
   }
})

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setWhereClauseWithWhereClause:@"age > 25 and addresses.city = 'New York'"];
[queryBuilder setRelatedWithRelated:@[@"addresses"]];
    
[[Backendless.shared.data of:[Person class]] findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *nycPeople) {
    for (Person *nycPerson in nycPeople) {
        NSLog(@"Name - %@", nycPerson.name);
        NSLog(@"Age - %li", (long)nycPerson.age);
        NSLog(@"City - %@", nycPerson.addresses.firstObject.city);
    }
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];

let queryBuilder = DataQueryBuilder()
queryBuilder.setWhereClause(whereClause: "age > 25 and addresses.city = 'New York'")
queryBuilder.setRelated(related: ["addresses"])
        
Backendless.shared.data.of(Person.self).find(queryBuilder: queryBuilder, responseHandler: { nycPeople in
    if let nycPeople = nycPeople as? [Person] {
        for nycPerson in nycPeople {
            print("Name - \(nycPerson.name ?? "")")
            print("Age - \(nycPerson.age)")
            print("City - \(nycPerson.addresses?.first?.city ?? "")")
        }
    }
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

const Backendless = require('backendless')
/*
 Or use `import Backendless from 'backendless'` for client side.
 If you don't use npm or yarn to install modules, you can add the following line
 <script src="//api.backendless.com/sdk/js/latest/backendless.min.js"></script>
 to your index.html file and use the global Backendless variable.
*/

Backendless.initApp('YOUR_APP_ID', 'YOUR_JS_API_KEY')

const PersonStorage = Backendless.Data.of('Person')

const findPersons = () => {
  const dataQuery = Backendless.DataQueryBuilder.create()
  dataQuery.setWhereClause('age > 25 and addresses.city = \'New York\'')
  dataQuery.setRelated('addresses')

  return PersonStorage.find(dataQuery)
}

const onSuccess = persons => {
  persons.forEach(person => {
    console.log(`Name - ${ person.name }`)
    console.log(`Age - ${ person.age }`)
    console.log(`City - ${ person.addresses[0].city }`)
  })
}

const onError = error => {
  console.error('Server reported an error: ', error.message)
  console.error('error code: ', error.code)
  console.error('http status: ', error.status)
}

Promise.resolve()
  .then(findPersons)
  .then(onSuccess)
  .catch(onError)

DataQueryBuilder dataQuery = DataQueryBuilder();
dataQuery.whereClause = "age > 25 and addresses.city = 'New York'";
dataQuery.related = ["addresses"];

Backendless.Data.of("Person").find(dataQuery).then((nycPeople) {
 nycPeople.forEach((person) {
   print("""Name - ${person['name']}
Age - ${person['age']}
City - ${person['addresses'][0]['city']}""");       
 });
});

Program output:

Name - Frankie
Age - 30
City - New York

The API is designed in a way that lets you combine query-based search with sorting, loading of relations, paging and many other data search-related features of Backendless.

How to Load Data Objects From the Server With an SQL Query

Leave a Reply Cancel reply

Related Articles