Complex sql in QueryParameters whereClause

[CalebMitcler]

I have 2 layers - "poles" and "poleAssembly". I want to display a subset of the poles layer where the features have a relationship with at least one feature in the poleAssembly layer. The relationship is defined by matching the globalID in the poles layer with the poleID in the poleAssembly table.

Here's an example of the data:

poles:
{name: "pole1", globalID: "a"}
{name: "pole2", globalID: "b"}
{name: "pole3", globalID: "c"}

poleAssembly:
{name: "assembly1", poleID: "b"}
{name: "assembly2", poleID: "b"}
{name: "assembly3", poleID: "c"}
{name: "assembly4", poleID: "d"}

In this case, the map should display only pole2 and pole3 because their globalIDs match at least one of the poleAssembly's poleIDs.

I'd like to accomplish this via a SQL statement in either QueryParameter.whereClause or FeatureLayer.definitionExpression.

I've tried the following-

if let polesTable = polesLayer.featureTable as? ServiceFeatureTable {
     var params = QueryParameters()
     params.whereClause = "EXISTS (SELECT 1 FROM poleAssembly WHERE 
                            poleAssembly.poleID = poles.GlobalID)"
     let results = try await polesTable.queryFeatures(using: params)
}

This throws error 400: "Cannot perform query. Invalid query parameters."

i've also tried this as whereClause "GlobalID IN (SELECT poleID FROM poleAssembly)" with same error

Any help or explanation on how to achieve this filter would be appreciated. Thanks!

[DiveshGoyal]

I do not know of a direct way to achieve this. You cannot use a where clause on one table that references another table.

Here's an option I can think of -
1) Query the pole assembly layer to find out all the poles they are attached to. If your data is coming from a feature service, you can make a custom REST web request with the `returnDistinctValues` parameter specified to get these values.
2) Take the values and either construct a where clause to use as a definitionExpression on the poles feature layer, or manually populate (ServiceFeatureTable.featureRequestMode = .manualCache) the poles feature table using that where clause.

`