match parent with subquery on 2 fields

[Antonio Dias]

Hi all,

I'm kind of new in OrientDb and having trouble with something that maybe is pretty simpl.

----+------+-------+------+----------+------+--------
#   |@RID|@CLASS |nodeId|instanceId|offset|status 
----+------+-------+------+----------+------+--------
0   |#9:105|Package|   A      |   IA        |  1     |RECEIVED
1   |#9:106|Package|   B      |   IC        |  1     |RECEIVED
2   |#9:108|Package|   B      |   IC        |  2     |RESOLVED
3   |#9:109|Package|   A      |   IA        |  2     |RESOLVED
----+------+-------+------+----------+------+--------

I'm using orientDb as a DocumentDb and storing data like above. I'm trying to find the lowest offset records with combination of (nodeId, instanceId). So for example, with the example data above i want to return records:

0   |#9:105|Package|   A      |   IA        |  1     |RECEIVED
1   |#9:106|Package|   B      |   IC        |  1     |RECEIVED

With the  query "select nodeId, instanceId, min(offset) from Package group by nodeId" i can get the information.

----+------+------+----------+----
#   |@CLASS|nodeId|instanceId|min
----+------+------+----------+----
0   |    null  |     A     |   IA     |   1  
1   |    null  |     B     |   IC     |   1  
----+------+------+----------+----

I have two problems now. First, if i include the @rid then the data returned seems wrong:

----+------+------+------+----------+----
#   |@CLASS|rid   |nodeId|instanceId|min
----+------+------+------+----------+----
0   |    null  | #9:109|  A     |   IA    |  1  
1   |    null  | #9:108|  B     |   IC    |  1  
----+------+------+------+----------+----
I understand that the resultset is not of Package type/class but the @rid do not match with the ones if i "select from Package".
I can "live" with this for now but the second problem is that to get a resultset of type/class Package i have think i would have to use subqueries but i can't figure out how.

For example, the query:

"select from Package let $results = (select  nodeId, instanceId, min(offset) from Package group by nodeId) where nodeId=$results.nodeId and instanceId=$results.instanceId"

returns an empty result set. I understand that the inner query returns a collection so it seems you cant match on columns like that.

I have a workaround which is to use all the columns in the select / projection with the names of the ODocument properties that i created (in java) and so and the query returns a similar object as the query "select from Package". But i would really like to avoid having to statically use the column names in the select.

I hope you guys understand my problem.