Querying Orient DB with 'joins'

Question

I've recently started looking at OrientDB coming from a relational background (Oracle RDBMS) and I'm struggling to query some data I've loaded into OrientDB.

I have 2 classes:

CREATE CLASS prices
CREATE PROPERTY prices.price_created_datetime DATETIME
CREATE PROPERTY prices.price_value DOUBLE

CREATE CLASS stocks
CREATE PROPERTY stocks.stock_symbol STRING
CREATE PROPERTY stocks.stock_prices LINKLIST prices

I'm loading some data by first running an UPSERT on the 'stocks' class, and then several times over the day adding prices:

UPDATE stocks ADD stock_prices = {json string of class:prices}

What I'd like to do is get all stocks.stock_symbol values and aggregate (using average) the prices.price_value within the last 24 hours (therefore filtering on prices.price_created_datetime).

I'm using the web based studio and I've toyed with a few different methods but I'm struggling to get the concept when most of my queries return nothing. I have OrientDB 2.1.9 running embedded in a Java application.

Any help would be appreciated.

Answer 1

I tried your case with this structure (like yours):

Class: stocks

Property: stocks.stock_symbol STRING

Property: stocks.stock_prices LINKLIST prices

Class: prices

Property: prices.price_created_datetime DATETIME

Property: prices.price_value DOUBLE

And here's the data:

To find all of stock symbols with relative prices average of the last 24 hours, I used this query:

select stock_symbol, $prices.averageLast24 as averagePricesLast24 from stocks 
let $prices = (select avg(price_value) as averageLast24 from (select price_value, price_created_datetime.asDatetime() as dataLast24 from prices) where eval('(sysdate() - dataLast24) / 3600000') < 24 and dataLast24 in $parent.current.stock_prices.price_created_datetime) 
unwind averagePricesLast24

and this is the output:

----+------+------------+-------------------
#   |@CLASS|stock_symbol|averagePricesLast24
----+------+------------+-------------------
0   |null  |bbb         |492345.5
1   |null  |ccc         |320167.0
----+------+------------+-------------------

Hope it helps