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 internalWhereClauseis 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 internalWhereClauseis not present, the contents of the INoperator 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 INoperator, 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.
The Landing Pages feature is going away and being replaced by page templates in UI Builder. Read on to learn more about this change, and why it makes our Landing Pages much more versatile! Back in July of 2018, we introduced the Landing Pages feature in version 5.1.0. This was a full 2 years before the…
[vc_row][vc_column][vc_column_text] Bubble.io is one of the most popular no-code app builders on the market. When you integrate a feature-rich no-code Backendless backend with your Bubble app, you get a visual relational database, user management, bulletproof security, highly-scalable serverless hosting, and much more. Building a mobile or web app used to be an enormous undertaking. Creating apps, or…
[vc_row][vc_column][vc_column_text] In the software as a service (SaaS) landscape, there are many variations of “____ as a service”. In this article, we will explain what mobile backend as a service, or MBaaS, means. At its core, MBaaS is an application platform that provides developers with all the tools they need to build and deploy apps…