Orient SQL - Filter result set using WHERE ?

87 views
Skip to first unread message

nightrise

unread,
Dec 7, 2015, 12:23:59 PM12/7/15
to OrientDB
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

unread,
Dec 8, 2015, 8:23:25 AM12/8/15
to OrientDB
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

unread,
Dec 9, 2015, 3:59:27 AM12/9/15
to orient-...@googlegroups.com
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

unread,
Dec 9, 2015, 1:49:34 PM12/9/15
to OrientDB
Thank you both for your answers! Both are quite useful, depending on the size of the returned result set. 

scott molinari

unread,
Dec 9, 2015, 2:44:14 PM12/9/15
to OrientDB
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

unread,
Dec 10, 2015, 3:37:48 AM12/10/15
to orient-...@googlegroups.com
Hi Scott,

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


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

unread,
Dec 10, 2015, 10:43:44 AM12/10/15
to OrientDB
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
Reply all
Reply to author
Forward
0 new messages