Orient SQL - Filter result set using WHERE ?

[nightrise]

Hey all,

I've got a bit of a semantic question about Orient SQL queries.

Take for example this very simple graph:

v(#12:1 User) --> e(#13:1 FriendOf) --> v(#12:2 User)

In other words, a given User with an rid of #12:1 is friends with another user with an rid of #12:2.

To get the friends of user #12:1, one might express this in Orient SQL like so:

SELECT EXPAND(both("FriendOf")) FROM #12:1

This query would return a result list comprised of the User with rid #12:2.

Now lets say I want to filter that result list by an additional criteria, like say a numeric value ("age"):

SELECT EXPAND(both("FriendOf")) FROM  #12:1 WHERE age >= 10

The above query would filter the CURRENT vertex (#12:1), NOT the result set. Which makes sense, but is there a way to apply the filter to the EXPAND(both("FriendOf")) result rather than the current vertex? I know I can do this with gremlin like so:

SELECT EXPAND(gremlin('current.both("FriendOf").has("age",T.gte,10)')) FROM #12:1

But the above does not seem to make use of indexes (at least not when I ask it to explain). For very large data sets, this is problematic. 

So is there a proper way to apply a WHERE statement to the resulting data set?

Thanks !

[Sung Eun Choi]

Hi,

What I would do is

SELECT * FROM (SELECT EXPAND(both("FriendOf")) FROM  #12:1) WHERE age >= 10


Hope this was what you have been looking for.

[Luigi Dell'Aquila]

Hi,

for a simple filter (single condition) you can use square bracket notation, eg.

SELECT EXPAND(both("FriendOf")[age >= 10]) FROM #12:1

It won't use indexes anyway, it will just retrieve the root node (#12:1) and then it will traverse edge pointers matching conditions during the traversa

Thanks

Luigi

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[nightrise]

Thank you both for your answers! Both are quite useful, depending on the size of the returned result set. 

[scott molinari]

Hey Luigi. Is that style of filtering documented anywhere? I don't recall seeing it and I personally would never have guessed it is possible.

Scott

[Luigi Dell'Aquila]

Hi Scott,

I did a quick check and I think it's missing, we should add a little note here at least

http://orientdb.com/docs/last/SQL-Query.html#projections

Just consider that square bracket filtering is very limited, it allows to filter only based on a single condition (eg. no AND/OR). 

Thanks

Luigi

2015-12-09 20:44 GMT+01:00 scott molinari <scottam...@googlemail.com>:

Hey Luigi. Is that style of filtering documented anywhere? I don't recall seeing it and I personally would never have guessed it is possible.

Scott

--

[scott molinari]

I'd do it myself, but I am honestly not sure what I should write or where to put it. 

If you want, let me know these things and I can add it for you.

Scott