orient DB search on datetime field and get result data from linked dataset

Question

I am quite new to OrientDB and have some trouble that keeps me for days now: I have two classes. "PAGES" is holding information about pages, "CHECKS" contains information about checks on these pages. They are connected by a 1 > n linkset called page2chck It looks like this

Class PAGES
+----+---------+---------------------------------+
| Id | Title   | Url                             |
+----+-------------------------------------------+
| 30 | Blahbla | http://www.test.com/test.html   |
+----+-------------------------------------------+
| 40 | sometxt | http://www.foo.org/dummy.html   |
+----+-------------------------------------------+

Class CHECKS
+---------------------+---------+
| Lastcheck           | Status  |
+-------------------------------+
| 2016-02-01 23:58:12 | OK      |
+-------------------------------+
| 2016-02-02 22:04:24 | OK      |
+-------------------------------+
| 2016-02-02 23:57:55 | ERR     |
+-------------------------------+
| 2016-02-01 23:59:01 | OK      |
+-------------------------------+

I created a linkset like this

CREATE LINK page2chck TYPE LINKSET FROM CHECKS.CH_PID to PAGES.Id INVERSE

Now I want to retrieve all Pages that do not have a check after 2016-02-03, and I want to show the last date they were checked and the status

What I tried was:

select Title, page2chck.Lastcheck, page2chck.Status from PAGES 
where date.asLong(page2chck.Lastcheck) < 1454540400

But it returns an empty result

However, to test the integrity of the relation I ran

select Title from PAGES where page2chck.CH_PID=30

which correctly returns "BlahBlah"

So I tried

page2chck.Lastcheck, page2chck.Status, Title from PAGES 
where page2chck.CH_PID=30

which returned

#   |@CLASS|page2chck|page2chck|Title                          
----+------+---------+---------+---------------------------------
0   |null  |[441]    |[441]    |BlahBlah               

So basically I have two problems here:

1. How can I run comparison on the date of a linked class and
2. how can I show the fields of this class?

Answer 1

CREATE LINK page2chck TYPE LINKSET FROM CHECKS.CH_PID to PAGES.Id INVERSE

Now From console

From Studio

You can use this query

SELECT Title, $checks[0].Lastcheck as Lastcheck , $checks[0].Status as Status FROM PAGES 
let $a = (select EXPAND(page2chck) from $parent.$current),
$checks= ( select Lastcheck, Status from $a where Lastcheck in 
( select max(Lastcheck) from $a where Lastcheck < DATE("2016-02-03 00:00:00")))

From console

From Studio

If you want to retrieve all Pages that do not have a check after 2016-02-03 you can use this query

select from (SELECT Title, $checks[0].Lastcheck as Lastcheck , $checks[0].Status as Status FROM PAGES 
let $a = ( select EXPAND(page2chck) from $parent.$current),
$checks= ( select Lastcheck, Status from $a where Lastcheck in ( select max(Lastcheck) from $a))
) where Lastcheck < DATE("2016-02-03 00:00:00")

Hope it helps.