Hi,
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):
* ITEMS
itemId | productId | quantity | created
* STOCKS
stockId | productId | stock | updated
* PRODUCTS
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:
SELECT
Items.productId,
products.name,
SUM(Items.quantity) AS ordersTotal,
Stocks.quantity,
(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?
