Select Query In filter with traverse Subquery not working

Hello,

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 structure?

Solution for query.
needed to use the OrientDB sql methods in the subquery, refer below Url for the methods documentation
https://orientdb.com/docs/last/sql/SQL-Methods.html

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