OreintDb Select Query IN Operator filter with traverse Subquery not working

Question

I'm trying to use the Traverse query in the sub-query to get the filtered data for the vertex, but not able to retrieve the data please find the below steps for the vertex setup

CREATE CLASS Territory IF NOT EXISTS EXTENDS V
CREATE PROPERTY Territory.name IF NOT EXISTS STRING (MANDATORY TRUE)
CREATE PROPERTY Territory.subTerritories IF NOT EXISTS LINKLIST Territory

CREATE CLASS Customer IF NOT EXISTS EXTENDS V
CREATE PROPERTY Customer.CustomerNo IF NOT EXISTS STRING (MANDATORY TRUE, NOTNULL TRUE)
CREATE PROPERTY Customer.territories IF NOT EXISTS LINKLIST Territory

Customer Vertex data insert INSERT INTO CUSTOMER (CustomerNo, territories) VALUES ("Cust_1", []), ("Cust_2", []), ("Cust_3", [])

Territory vertex data insert INSERT INTO Territory (name, subTerritories) VALUES ("All Territories", []), ("United States", []), ("East Coast", []), ("West Coast", [])

Then first Update the Territories records by updating their subTerritories property value follow below structure

  All Territories 
    United States (sub territory of All territory)
        East Coast  (sub territory of United states) 
        West Coast  (sub territory of United states)

Update records as per the below scenarios

1. Add United States rid to the All Territories subTerritories list
2. Add East Coast and West Coast rid's to the United States Record's subsTerritories list
3. Add the East Coast Territory rid to 2 customers records territories property and West Coast Territory to remaining single record

Now as per our requirement

• if customers are filtered by the east coast territory id then we should get the 2 customers
• if Customer filtered by the United States territory id then it should return both east coast and west coast customers data

so for getting the territories rid's list we're using the traverse Query e.g. if you pass the United states territory rid in below query then it will return the 3 records as per above data setup i.e united states, east coast and west coast data Syntax: SELECT @rid FROM (TRAVERSE subTerritories FROM <UNITED_STATES_TERRITORY_RID>) e.g. SELECT @rid FROM (TRAVERSE subTerritories FROM #23:0)

this query we needs to use as the subquery for filtering the customers records

If we try the normal customer query with In Operator and the territory id mentioned then we get the data select * from customer WHERE territories.@rid IN [#24:0]

but instead of the hardcoded territory Id we need the traverse query as subquery to get the territory records ids

Synatx: select * from customer WHERE territories IN (SELECT @rid FROM (TRAVERSE subTerritories FROM <UNITED_STATES_TERRITORY_RID>)) e.g. select * from customer WHERE territories IN (SELECT @rid FROM (TRAVERSE subTerritories FROM #23:0))

but with this query we're not getting single customer record, as it should return the 3 customer records as east coast customers are under the united states territory

any suggestions on the above sub query improvements ?

Answer 1

Needed to use the OrientDB SQL methods (asList() in this case) in the subquery.

SELECT FROM customer WHERE territories IN (SELECT @rid.asList() FROM (TRAVERSE subTerritories FROM [#65:0,#66:0]))