SQL query result using Views


I have been playing with the new Views feature and while I find it very useful, unfortunately I haven’t been able to figure out how to produce my desired result. I have the following three tables (simplified):

itemId | productId | quantity | created

stockId | productId | stock | updated

productId | name

Using these three tables, I want to produce a view with the following structure…

productId | ordersTotal | stock | balance | name

…where the ordersTotal column is the aggregate sum of the ITEMS.quantity column grouped by ITEMS.productId where the value of ITEMS.created is equal or greater than the value of STOCKS.updated; and the balance column is the difference between the value for STOCKS.stock and the ordersTotal column (or its equivalent result).

Usually, I would write a query like this in SQL that would get me the result:

    SUM(Items.quantity) AS ordersTotal,
    (Stocks.quantity - SUM(Items.quantity)) AS balance
FROM Items
INNER JOIN Stocks ON Stocks.productId = Items.productId
INNER JOIN Products ON Products.objectId = Items.productId
WHERE Items.created >= Stocks.updated
GROUP BY Items.productId

How can achieve the same result with the Views interface in Backendless?

Hi Juan,

Have you established relationships between the tables in your schema? If you could attach a screenshot of the schema diagram as it is generated by Backendless, it would make it much easier to understand the structure.


Hey Mark,

I actually managed to get the view working by establishing the appropriate relationships between the tables. However, I noticed that in some situations I had to establish two 1-1 relationships between two tables: e.g. a record in the ITEMS has a 1-1 relationship with a record in the PRODUCTS table, while at the same time a record in the PRODUCTS table has a 1-1 relationship with a record in the ITEMS table.

This is my current structure:

Another thing I couldn’t figure out was how to add a calculated column to the view (that is, a column that is the result of some operation between two other columns instead of an aggregation of a single column).


Hello @Juan_Giraldo

If the columns present in one table then you are able to create generated column Functions and Examples of Generated Columns | Backendless

Also you can use it in query if


I have created an internal discussion BKNDLSS-25081 to add this feature to view