CODEWITHSUNDEEP
orientdborientdb-2.1orient-sql
nightrise
nightrise

Reputation: 57

Orient SQL - Filter result set using WHERE?

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 !

Upvotes: 1

Views: 258

Answers (1)

peak
peak

Reputation: 116740

... is there a way to apply the filter to the EXPAND(both("FriendOf")) result rather than the current vertex?

The simple answer is to embed your basic "SELECT EXPAND ..." within another SELECT, i.e.

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

By the way, on my Mac, the above took .005s compared to over 2s for the Gremlin version. There must be a moral there somewhere :-)

Upvotes: 0

Related Questions