Skip to content

Working wih Properties

A data table in Backendless consists of columns which can be defined using Backendless Console or with Dynamic Schema Definition. When an object is retrieved from the database, the columns are represented by object properties. Consider the following table schema for a data table called Person:

person-table-schema

The data for the table may look as shown below:

person-table-data

When you use the data retrieval API, server returns all properties for each object (the response below is shown in the format that a REST client would receive, however, when you use the SDK the server returns objects, rather than a JSON string):

[
  {
    "phoneNumber": "(718)987-2233",
    "created": 1495738464193,
    "name": "Amber",
    "dateOfBirth": 1049864400000,
    "updated": 1586451729000,
    "objectId": "472289B3-A7EA-EF20-FFEA-86D6A4457D00",
    "ownerId": null,
    "___class": "Person",
    "location": {
      "type": "Point",
      "coordinates": [
        -76.18894725,
        39.8744915
      ],
      "srsId": 4326,
      "___class": "com.backendless.persistence.Point"
    }
  },
  { .... },
  { .... },
  { .... },
  { .... },
  { .... },
]

This is meant to demonstrate that by default the server returns all properties. There are some scenarios when you need to retrieve one or more specific properties. The section of the guide describes the APIs you can use manage which properties the server should return or ignore.

Requesting Specific Properties

Given the schema and the data shown above, suppose you need to get only the name and dateOfBirth properties. To do this, the following API can be used:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder addPropertiesWithProperties:@[@"name", @"dateOfBirth"]];
[[Backendless.shared.data ofTable:@"Person"] findWithQueryBuilder:queryBuilder responseHandler:... errorHandler:...];
let queryBuilder = DataQueryBuilder()
queryBuilder.addProperties(properties: "name", "dateOfBirth")
Backendless.shared.data.ofTable("Person").find(queryBuilder: queryBuilder, responseHandler: ..., errorHandler: ...)

addProperties method instructs the server to return values for the specified columns.

For the request shown above, the server returns the following response:(again, showing in the REST format for simplicity. For the SDK you use, the response will include objects with the same properties as below):

[
  {
    "name": "Amber",
    "dateOfBirth": 1049864400000,
    "objectId": "472289B3-A7EA-EF20-FFEA-86D6A4457D00",
    "___class": "Person"
  },
  {
    "name": "Jeff",
    "dateOfBirth": 845528400000,
    "objectId": "781C695D-445B-53E0-FFC0-8EC66221DC00",
    "___class": "Person"
  },
  { ... },
  { ... },
  { ... },
  { ... }
]

In addition to requesting a "block" of properties with the addProperties method you can also request a specific property using the addProperty method:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder addPropertyWithProperty:@"columnName1"];
[queryBuilder addPropertyWithProperty:@"columnName2"];
[[Backendless.shared.data ofTable:@"Person"] findWithQueryBuilder:queryBuilder responseHandler:... errorHandler:...];
let queryBuilder = DataQueryBuilder()
queryBuilder.addProperty(property: "columnName1")
queryBuilder.addProperty(property: "columnName2")
Backendless.shared.data.ofTable("Person").find(queryBuilder: queryBuilder, responseHandler: ..., errorHandler: ...)

Excluding Properties

Suppose your schema has a lot of properties and you need to get all of them, with a few exclusions. This can be done using the excludeProperty or excludeProperties methods

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder excludeProperties:@[@"location", @"created", @"updated", @"ownerId"]];
[[Backendless.shared.data ofTable:@"Person"] findWithQueryBuilder:queryBuilder responseHandler:... errorHandler:...];
let queryBuilder = DataQueryBuilder()
queryBuilder.excludeProperties("location", "created", "updated", "ownerId")
Backendless.shared.data.ofTable("Person").find(queryBuilder: queryBuilder, responseHandler: ..., errorHandler: ...)

For the request shown above, the server returns the following response:

[
  {
    "phoneNumber": "(718)987-2233",
    "name": "Amber",
    "dateOfBirth": 1049864400000,
    "objectId": "472289B3-A7EA-EF20-FFEA-86D6A4457D00",
    "___class": "Person"
  },
  {
    "phoneNumber": "(314)888-3322",
    "name": "Jeff",
    "dateOfBirth": 845528400000,
    "objectId": "781C695D-445B-53E0-FFC0-8EC66221DC00",
    "___class": "Person"
  },
  { ... },
  { ... },
  { ... },
  { ... }
]

Dynamic Properties

A dynamic property is one that does not explicitly belong to a schema. The value of a dynamic property is calculated based on the expression you provide. Expressions supported by Backendless can use special functions and basic arithmetic operations.

Consider the example below. The schema has the dateOfBirth column. Using that value, you can request Backendless to calculate the age of each person and return it as a dynamic property called age. This is what the API request would look like:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder addPropertyWithProperty:@"2020 - YEAR(dateOfBirth) as age"];
[[Backendless.shared.data ofTable:@"Person"] findWithQueryBuilder:queryBuilder responseHandler:... errorHandler:...];
let queryBuilder = DataQueryBuilder()
queryBuilder.addProperty(property: "2020 - YEAR(dateOfBirth) as age")
Backendless.shared.data.ofTable("Person").find(queryBuilder: queryBuilder, responseHandler: ..., errorHandler: ...)

There are several new elements in the API:

  • There is the addProperty method which adds a dynamic property;
  • The addProperty method references an expression with an arithmetic operator (subtraction) and the YEAR function which returns the year number for a value in the referenced column;

Notice the expression also includes the part which asks the server to assign a name to the calculated property:

as age

For the request documented above, the server returns the following:

[
  {
    "objectId": "472289B3-A7EA-EF20-FFEA-86D6A4457D00",
    "age": 17,
    "___class": "Person"
  },
  {
    "objectId": "781C695D-445B-53E0-FFC0-8EC66221DC00",
    "age": 24,
    "___class": "Person"
  },
  { ... },
  { ... },
  { ... },
  { ... }
]

This chapter includes a reference of all functions supported by dynamic properties.

Both addProperties and addProperty methods can reference columns from the related tables. Consider the following example. There are two tables: City and Country. The City table's schema includes a relation column called Country. The column represents a one-to-one relation with the Country table:

The City table schema:

city-schema

The Country table schema:

country-schema

Suppose you need to query the City table and retrieve the Name of the city and the Name of the country it belongs to. Below is the sample API to retrieve that data:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder addPropertyWithProperty:@"Name"];
[queryBuilder addPropertyWithProperty:@"Country.Name as CountryName"];
[[Backendless.shared.data ofTable:@"City"] findWithQueryBuilder:queryBuilder responseHandler:... errorHandler:...];
let queryBuilder = DataQueryBuilder()
queryBuilder.addProperty(property: "Name")
queryBuilder.addProperty(property: "Country.Name as CountryName")
Backendless.shared.data.ofTable("City").find(queryBuilder: queryBuilder, responseHandler: ..., errorHandler: ...)

The Country.Name as CountryNamereferences the Country column in the City table and then the Name column in the Country table. Additionally using the "as CountryName" the resulting value is returned in the CountryName column.

Adding All Properties

As you can see from the sample server responses shown above, when specific properties are requested, the server returns only those properties. At times, it is necessary to get all available properties and then some dynamic or related object properties. This can be done using the API shown below:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder addAllProperties];
[queryBuilder addPropertyWithProperty:@"columnName or expression"];
[[Backendless.shared.data ofTable:@"Person"] findWithQueryBuilder:queryBuilder responseHandler:... errorHandler:...];
let queryBuilder = DataQueryBuilder()
queryBuilder.addAllProperties()
queryBuilder.addProperty(property: "columnName or expression")
Backendless.shared.data.ofTable("Person").find(queryBuilder: queryBuilder, responseHandler: ..., errorHandler: ...)

The addAllProperties() method instructs the server to return all properties and then add other properties requested with the addProperty method(s).

Functions for Dynamic Properties

Functions enable transformation of property values. Using these functions, you can extract the date, month, or year from your DATETIME columns, convert STRING literals to upper/lower case, etc. Functions can be grouped together. For instance, if a function returns a string, it can be used as an argument for another function that expects a string argument. For example:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder addPropertyWithProperty:@"REVERSE( UPPER( columnName ) )"];
[[Backendless.shared.data ofTable:@"YOUR-TABLE"] findWithQueryBuilder:queryBuilder responseHandler:... errorHandler:...];
let queryBuilder = DataQueryBuilder()
queryBuilder.addProperty(property: "REVERSE( UPPER( columnName ) )")
Backendless.shared.data.ofTable("YOUR-TABLE").find(queryBuilder: queryBuilder, responseHandler: ..., errorHandler: ...)

DATETIME Functions

  • DATE( dateTimeColumnName ) – extracts the date part of a DATETIME column value.
  • TIME( dateTimeColumnName ) – extracts the time portion of a DATETIME column value.
  • WEEK( dateTimeColumnName ) – returns the week number of a DATETIME column value
  • WEEK( dateTimeColumnName, mode ) –  same as above, but allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. Second argument mode == 0 ~ 7.  The mode argument is:
mode
First day of week
Range
Week 1 is the first week...
0
Sunday
0-53
with a Sunday in this year
1
Monday
0-53
with 4 or more days this year
2
Sunday
1-53
with a Sunday in this year
3
Monday
1-53
with 4 or more days this year
4
Sunday
0-53
with 4 or more days this year
5
Monday
0-53
with a Monday in this year
6
Sunday
1-53
with 4 or more days this year
7
Monday
1-53
with a Monday in this year
  • MONTH( dateTimeColumnName ) – returns the month for a DATETIME column value, in the range 1 to 12 for January to December, or 0 for dates such as ‘0000-00-00’.
  • QUARTER( dateTimeColumnName ) – returns the quarter of the year for a DATETIME column value, in the range 1 to 4.
  • YEAR( dateTimeColumnName ) – returns the year for a DATETIME column value, in the range 1000 to 9999, or 0 for the “zero” date.
  • DAYOFMONTH( dateTimeColumnName ) – returns the day of the month for a DATETIME column value, in the range 1 to 31, or 0 for dates such as ‘0000-00-00’.
  • DAYOFWEEK( dateTimeColumnName ) – returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday)
  • DAYOFYEAR( dateTimeColumnName ) – returns the day of the year (1-366)
  • HOUR( dateTimeColumnName ) – returns the hour for time. The range of the return value is 0 to 23 for time-of-day values.
  • MINUTE( dateTimeColumnName ) – returns the minute for time, in the range 0 to 59
  • SECOND( dateTimeColumnName ) – returns the second for time, in the range 0 to 59.

STRING Functions

  • TRIM( stringORTextColumnName ) – returns a string value from the STRING or TEXT column with leading and trailing spaces removed.
  • REVERSE( stringORTextColumnName ) – returns a string value with the characters positioned in the reversed order.
  • UPPER( stringORTextColumnName ) – returns a string value with all alphabetical characters converted to the upper case equivalent.
  • LOWER( stringORTextColumnName ) – returns a string value with all alphabetical characters converted to the lower case equivalent.