Retrieving objects

This operation retrieves data objects from a database table. The retrieval is performed according to the provided options, which allow you to:

• Retrieve objects with a query
• Using paging
• Using sorting
• With related objects

{
  "isolationLevelEnum" : "REPEATABLE_READ" | "READ_COMMITTED" | "READ_UNCOMMITTED" | "SERIALZABLE",
  "operations": [ 
    {
      "operationType": "FIND",
      "table": "TABLENAME",
      "opResultId": "OPRESULT-ID",
      "payload": {
         "pageSize" : VALUE,
         "offset" : VALUE,
         "properties" : ["prop1","prop2"],
         "whereClause" : "where clause",
         "groupBy" : ["prop1","prop2"]
         "havingClause" : "having clause",
         "queryOptions" : {
            "sortBy" : ["prop1","prop2"],
            "related" : ["rel11","rel2"],
            "relationsDepth" : VALUE,
            "relationsPageSize" : VALUE
         }
      }
    }
  ]
}

where:

Argument	Description
"operationType"	The value must be "FIND", this designates the operation as "retrieving objects".
"table"	The value must contain the name of the table where the object will be saved. The table must exist in the database before the transaction is executed.
"opResultId"	This is an optional property. If present, must contain an Id for this operation. An operation Id is a free-form string that must be unique within the transaction. If the result of this operation is to be used as an input in another operation within the same transaction, the Id will be used to reference the result.
"payload"	The value is mandatory. At the very minimum it must contain an empty JSON object - {}. Additional properties listed below may be added to form a query.
"pageSize"	A numeric value. If set, specifies how many objects the server should return starting from the offset index. The first object has the index of 0.
"offset"	A numeric value. If set, specifies from what index the objects should be returned. The first object has the index of 0.
"properties"	A JSON array. If set, contains a list of property/column names as string values instructing the server to return the specified properties/columns.
"whereClause"	A string value. If set, is a query which identifies the objects to be returned. The server returns only the objects which match the query. See the Search with the Where Clause section for more information.
"groupBy"	A JSON array. If set, contains a list of property/column names to group the result by. Used with aggregate functions.
"havingClause"	A string value. May be used in conjunction with the groupBy property to enable additional data filtering option for aggregate functions. See the Data Filtering section for additional details.
"queryOptions" > "sortBy"	A JSON array. If set, contains a list of property/column names the result returned by the server will be sorted by.
"queryOptions" > "related"	A JSON array. If set, contains a list of related property/column names for which the related child objects will be returned along with each parent object.
"queryOptions" > "relationsDepth"	A numeric value. If set, specifies the depth of related objects the server should include into the response. For example, if relationsDepth is set to 2, and the chain of relations is objects from table A have related objects from table B and objects from B have related objects from table C, then the returned objects will include A's which reference B's which reference C's.
"queryOptions" > "relationsPageSize"	A numeric value. If set, specifies how many related objects should be returned. The related objects are returned when any of the following conditions is true: (1) the auto-load option is enabled in Backendless Console in the table referenced by the "table" property; (2) "queryOptions" > "related" is present in the request; (3) "queryOptions" > "relationsDepth" is present in the request.

Important

You can retrieve a single object by setting the pageSize option to 1. However, remember the method still returns a collection of objects, therefore if you need to get only one object, you need to use the "resultIndex" property when referencing this operation's result

Return Value

The operation returns a collection of retrieved objects. The collection can be used as an argument in other operations in the same transaction. There are two ways to reference a result from a previous operation - using either {"___ref":true, "opResultId":"VALUE"} to reference the actual collection or {"___ref":true, "opResultId":"VALUE", "resultIndex":VALUE, "propName":"VALUE"} to reference an individual object from the collection or a property value of an object.

Example

The example below demonstrates retrieval of an object and updating its property in a transaction:

Request:

URL:

POST https://xxxx.backendless.app/api/transaction/unit-of-work

Request header:

Content-Type:application/json

Request body:

{
  "isolationLevelEnum" : "SERIALZABLE",
  "operations": [
    {
      "operationType": "FIND",
      "table": "Country",
      "opResultId": "findCountry",
      "payload": {
        "whereClause":"name = 'India'",
        "pageSize":1
      }
    },
    {
      "operationType":"UPDATE",
      "table": "Country",
      "payload": {
         "objectId": { 
             "___ref":true,
             "opResultId":"findCountry",
             "resultIndex":0,
             "propName": "objectId"
         },
        "population": 1339000000
      }
    }
  ]
}

Response:

{
  "success": true,
  "error": null,
  "results": {
    "findCountry": {
      "type": "FIND",
      "result": [
        {
          "name": "India",
          "created": 1579825096648,
          "updated": 1585974596000,
          "objectId": "1359E34B-D4B3-C161-FFD5-928956F78500",
          "population": null,
          "ownerId": null,
          "___class": "Country"
        }
      ]
    },
    "updateCountry1": {
      "type": "UPDATE",
      "result": {
        "population": 1339000000,
        "updated": 1585975072000,
        "___class": "Country",
        "created": 1579825096648,
        "ownerId": null,
        "objectId": "1359E34B-D4B3-C161-FFD5-928956F78500",
        "name": "India"
      }
    }
  }
}

Codeless Reference

where:

Argument	Description
operation id	Unique identifier of the current operation which can be used later on in other subsequent operation within the same transaction. Refer to the Operation Result topic for more information.
table name	Name of the data table where a new record must be found.
where clause	A search query used by the server it to determine objects matching the condition. Refer to the Search With The Where Clause topic for more information.
having clause	Sets a condition on a aggregate function to filter groups.
relations	Name of the related property to load. For example, if table employees has a relation column homeAddress pointing to an object in the Address table, the value of the parameter would be homeAddress. The syntax allows to add relations of relations. For example, if the same Address table has a relation country pointing to the Country table, then homeAddress.country would instruct the backend to load the related Country object.
properties	Names of the properties/columns for which  to load the corresponding values.
exclude properties	Names of the properties/columns that should not be included in the response.
relations depth	Depth of the relations to include into the response.
relations page size	Sets the number of related objects returned in the response.
sort by	Lists properties by which the returned collection should be sorted by.
group by	Sets the name of the columns to group the results by.
page size	Sets the page size which is the number of objects to be returned in the response.
page offset	Zero-based index of the object in the persistent store from which to run the search. This parameter should be used when implementing paged access to data. Suppose the first request returned 20 objects (if pageSize is set to 20) and there are 100 objects total. The subsequent request can set offset to 20, so the next batch of objects is loaded sequentially.
distinct	Used to return only unique values from a column.
file reference prefix	This property allows replacing the default URL file prefix. For instance, when the operation returns a path to a file stored on the server ("https://yourdomain.backendless.app/my-file.jpg"), then you can reconstruct it by passing the new file name that must start with a slash - "/wonderful_forest.jpg". It is useful when you want the client application to open a specific file locally.
return operation reference	Optional parameter. When this box is checked, after this operation is executed its reference is returned in the response as an object containing transaction details.

Consider the structure of the data table called Person:

To create a transaction you must use the following additional Codeless blocks:

1. Create Transaction Codeless block is important, since it creates an empty object which is used as a placeholder for transaction information. Refer to the Transaction Isolation topic to learn more about the options of the isolation property.  This empty object must be saved to a variable.

1. All operations stored inside a transaction must have a specific order. The following Codeless block sets this order for different operation types. The transaction property expects an empty object created with the Create Transaction Codeless block. This mandatory empty object is used to store transaction information. In the context of this description, the variable that was set for the Create Transaction Codeless block must be used in the transaction property to specify the empty object.

This block expects different operation types, such as "Find",``"Create", "Upsert", "Update" and "Delete".  This topic describes the "Find" operation type used in conjunction with these additional Codeless blocks. The example below shortly highlights how this block accepts the "Find" operation and also provides insight how the previous blocks are used to create the required logic structure:

As it was mentioned earlier, you need a variable(e.g. myTx)  that will store the empty object. Then this empty object myTx must be passed to the Add Operations to Transaction Codeless block, so that the logic can store the transaction details. And then inside this block you must declare operations; this example uses only one operation of the "Create" type.

Now lets provide data to properties of the "Find" Operation Codeless block. The table name argument must reference the data table where an object is saved. The operation id property must be set to the value of your choice, while the where clause argument expects a condition to find a specific object in the data table.

1. To run the logic above you must use an additional Codeless block that initiates the operation. The transaction property expects an object containing the transaction details(e.g. myTx variable).

The following example combines all Codeless blocks described above into a logic that finds objects matching the specified condition..

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

This example contains an additional variable called opRef(operation reference), that was added intentionally to highlight the operation reference returned after this Codeless logic runs:

Suppose you need to run two operations "Find" and "Update" in one transaction, to update an object that matches the where clause condition. Consider the data table Person presented below:

But in order to chain two operations in one transaction, you have to use the 'Get Operation Result Reference' Codeless block:

where:

Argument	Description
operation reference	Must contain an operation``reference identifier of another operation whose result should be used as the input argument.
index	An optional property. Must contain a numeric (integer) value. If the result of the operation referenced by operation``reference is a collection, this value identifies an element from the collection. The referenced element is used as an argument, unless the property``name is also set. See the "A Value in the Result" section below for more details.
property name	References a property within the result. If the result identified by operation``reference identifier is a single object, then the property identified by the property``name is referenced. Otherwise, if the result identified by operation``reference identifier is a collection, the index must be used to reduce the collection to a single object first and then property``name is applied. See the "A Value in the Result" section below for more details.

The example below sets the where clause condition to find all objects whose values in the age column are less or equals to 30.  When objects matching the condition are found, the "Update" operation takes place and updates the value in the age column to 35 for an object that has the index position 1 in the response.

After the Codeless logic runs, the transaction updates the value in the age column to 35 of one object matching the specified condition. As you can see, the object with the value 'Sam' has been updated.