Convert a complex relational SQL query so that it runs in OrientDB

Question

I am a newbie to OrientDB. I have a table structure as shown below. I have tried out a lot of queries but still cannot figure out how to implement the following SQL query in OrientDB. I need to join data from 4 different classes. Any help is greatly appreciated.

select Users.Name as uName,
   Shops.Name as shopName,
   ShopTotalPurchases.Total

from Users, Shops, Purchases,ShopTotalPurchases 

where Users.Id=10 

and Purchases.UserId=Users.Id

and Purchases.Date="date2"

and ShopTotalPurchases.Date="date2"

and ShopTotalPurchases.ShopId = Shops.Id

> reldb> SELECT * FROM Users;
+----+--------+-------+
| Id | Name   | Phone |
+----+----------------+
| 10 | User 1 | 1111  |
+----+----------------+
| 20 | User 2 | 2222  |
+----+----------------+


> reldb> SELECT * FROM Shops;
+----+--------+-------+
| Id | Name   | Phone |
+----+----------------+
| 30 | Shop 1 | 1111  |
+----+----------------+
| 40 | Shop 2 | 2222  |
+----+----------------+

> reldb> SELECT * FROM Purchases; [unique per combo of UserId, ShopId and Date]
+----+--------+--------+--------+------+-----------+
| Id | UserId | Item   | ShopId | Date | ItemPrice |
+----+--------+-----------------+------+-----------+
|  0 | 10     | First  |  30    |date1 |   100     |
+----+--------+--------+--------+------+-----------+
|  1 | 10     | Second |  30    |date2 |   200     |
+----+--------+--------+--------+------+-----------+
| 21 | 10     | Third  |  40    |date3 |   300     |
+----+--------+--------+--------+------+-----------+
| 41 | 20     | Fourth |  40    |date4 |   400     |
+----+--------+--------+--------+------+-----------+
| 82 | 20     | Fift   |  30    |date5 |   500     |
+----+--------+--------+--------+------+-----------+

> reldb> SELECT * FROM ShopTotalPurchases;
+----+--------+--------+------+
| Id | Total  | ShopId | Date |
+----+--------+--------+------+
|  0 | 1000   |  30    |date1 |
+----+--------+--------+------+
|  1 | 2000   |  30    |date2 |
+----+--------+--------+------+
| 21 | 3000   |  40    |date2 |
+----+--------+--------+------+

Answer 1

you can use this query:

select $a.Name as uName, $b.Name as shopName, $c.Total as Total from Purchases
let $a = (select Name from User where Id = 10 and Id in $parent.current.UserId),
    $b = (select Name from Shops where Id in $parent.current.ShopId),
    $c = (select Total from ShopTotalPurchases where Date = 'date2' and ShopId in $parent.current.ShopId)
where Date = 'date2' unwind uName, shopName, Total

Console output:

----+------+-----+--------+------
#   |@CLASS|uName|shopName|Total
----+------+-----+--------+------
0   |null  |User1|Shop1   |2000.0
----+------+-----+--------+------