Data retrieval in Backendless has gotten significantly more powerful with the release of version 4.2.0. We added support for subquery into the syntax of whereClause . The improvement allows your apps to run two queries in the context of one request. The first query identifies a set of objects which is fed into the second query. Consider the following data schema:
Suppose your app needs to get all blog posts where the person who posted a comment has a specific email address (say @backendless.com ). Using subqueries, it can be done with the following whereClause sent to the BlogPost table:
objectId in (Comment[author.email LIKE '%@backendless.com'].blogPost.objectId)
Let’s review how this whereClause is processed by Backendless:
- Backendless detects that the query at the top level uses the in() operator.
- It checks if the contents of the in() operator have the format of a subquery and if so, evaluates it accordingly.
- The subquery contains an “internal whereClause”, which is:
author.email LIKE '%@backendless.com'
The internal whereClause is applied to the objects in the Comment table.
- For all found Comment objects, Backendless gets a list of values for the blogPost.objectId column.
- The resulting set is fed into the in() operator which fetches the final set of objects from the BlogPost table.
General SubQuery Syntax
The general syntax for subqueries is:
How it works:
- If internalWhereClause is present, it is executed in the table identified by TableName.
- In the resulting set of records, values must be selected for the column identified by columnOrRelatedColumnName.optionalColumnName.
- If internalWhereClause is not present, the contents of the IN operator have the following syntax. It represents the entire set of values identified by the specified column: TableName.columnOrRelatedColumnName.optionalColumnName
- The resulting set of values is used in the
IN operator, thus the final query ends up being:
searchColumnOrRelatedColumnName IN ( value1, value2,, valueN )
Consider the following data schema:
- Get all
OrderItem objects for a customer from
New York :
objectId in (Order[customer.address = 'New York'].items.objectId)
- Get all
Customer objects who spent more than 1.99 on an item:
objectId in (Order[items.itemPrice > 1.99].customer.objectId )
Please let us know if you can think of additional examples you would like us to demonstrate.