Working with 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
:
The data for the table may look as shown below:
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:
const queryBuilder = Backendless.DataQueryBuilder.create()
queryBuilder.addProperties('name', 'dateOfBirth')
Backendless.Data.of('Person').find(queryBuilder)
The addProperties( "name", dateOfBirth" )
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:
const queryBuilder = Backendless.DataQueryBuilder.create()
queryBuilder.addProperty('columnName1')
queryBuilder.addProperty('columnName2')
Backendless.Data.of('Person').find(queryBuilder)
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 excludeProps
methods
const queryBuilder = Backendless.DataQueryBuilder.create()
queryBuilder.excludeProps('location', 'created', 'updated', 'ownerId')
Backendless.Data.of('Person').find(queryBuilder)
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:
const queryBuilder = Backendless.DataQueryBuilder.create()
queryBuilder.addProperty('2020 - YEAR(dateOfBirth) as age')
Backendless.Data.of('Person').find(queryBuilder)
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 theYEAR
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.
The as propertyName
part of the query is called an alias. For convenience an alias may be reused in the where clause and the sorting option. For example, a query may be composed with the following parts:
property=2020-YEAR(dateOfBirth) as age
- where clause:
age > 20
- sortBy:
age
Related Object 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:
The Country
table 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:
const queryBuilder = Backendless.DataQueryBuilder.create()
queryBuilder.addProperty('Name')
queryBuilder.addProperty('Country.Name as CountryName')
Backendless.Data.of('City').find(queryBuilder)
Country.Name as CountryName
references 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:
const queryBuilder = Backendless.DataQueryBuilder.create()
queryBuilder.addAllProperties()
queryBuilder.addProperty('columnName or expression')
Backendless.Data.of('Person').find(queryBuilder)
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:
const queryBuilder = Backendless.DataQueryBuilder.create()
queryBuilder.addProperty('REVERSE( UPPER( columnName ) )')
Backendless.Data.of('YOUR-TABLE').find(queryBuilder)
For a complete list of supported functions see the Database Functions section of this guide.