OrientDB SQL - traverse while keeping edges weight

Question

Lets assume the following model in OrientDB graph:

I have a Profile vertex. Profiles are connected with 2 edges: Liked and Commented. Both edges have a "value" field indicating the count of the action (or the "weight" of the edge).

So, if user A commented 3 times on posts by user B there will be a Commented edge from user A to user B with value = 3.

Now, say I want to get all the users that interacted with user B (either liked or commented), sorted by the weight of the interaction. I can do that with the following SQL:

select * from (traverse out from   
(select out, sum(value) as value from    
(traverse * from (select from Profile where username="B") while $depth < 3)    
where @class="Liked" or @class="Commented" group by out order by value desc)   
while $depth < 2  ) where @class="Profile" )

But what if I want to know also the weight of the interaction? How do I propagate up the "value" while doing the last traverse?

Edit

According to the suggestion, a simplified version of this query will be:

select expand(out) from (    
    select out, sum(value) as value from (    
       select expand(inE("Liked", "Commented")) from Profile 
           where username="B"    
    ) group by out order by value desc 
)

But I still can't find a way to use LET to insert the value into the outer expanded object. $parent does not seem to point to the object that is expanded on the most outer select.

Edit 2

I'm Playing with $parent in every way I can think of. I don't see how you can use it in this case. Again - the problem I'm trying to solve is how to pass the sum(value) to the outer result set. I don't see a way of using LET for it when doing a GROUP BY, and I also don't see a way of using LET when the outer most select is doing an expand (since you can't do other projections together with expand).

Also, the results of using $current do not seem to be what is expected. For example, the following query:

select expand($v) from    
   (select from    
      (select expand(inE("Liked", "Commented")) from Profile where @rid=#11:0)   
    let $v  = $current   
   )

Returns this:

{  
   "result" : [{  
       "@type" : "d",
        "@rid" : "#14:4",
        "@version" : 2,
        "@class" : "Commented",
        "value" : 1,
        "out" : "#11:165",
        "in" : "#11:0"
    }, {
        "@type" : "d",
        "@rid" : "#14:4",
        "@version" : 2,
        "@class" : "Commented",
        "value" : 1,
        "out" : "#11:165",
        "in" : "#11:0"
    }, {
        "@type" : "d",
        "@rid" : "#14:4",
        "@version" : 2,
        "@class" : "Commented",
        "value" : 1,
        "out" : "#11:165",
        "in" : "#11:0"
    }
]
}

The same node over and over again, instead of all the edges, which is what I would expect.

Lvca Lvca · Accepted Answer · 2013-11-11T10:50:37

I see you're using an old version of OrientDB. With more recent versions you can simplify it by. Example: original query:

select * from (
 traverse out from (
  select out, sum(value) as value from (
   traverse * from (
    select from Profile where username="B"
   ) while $depth < 3
  ) where @class="Liked" or @class="Commented" group by out order by value desc
 ) while $depth < 2 
) where @class="Profile" )

You could skip some step by using out()/in()/both() passing the Edge's labels/class like:

select expand( out(["Liked","Commented]) ) from Profile where username="B"

However to pass the value you can use variables with LET clause. Example:

select from XXX let $parent.a = value

In this way you set the variable "a" into the upper level context, but you could do also:

select from XXX let $parent.$parent.a = value

To set it 2 levels up.

OrientDB SQL - traverse while keeping edges weight

2 Answers