"Advanced" query selection of entities through a relationship

Raul_Jimenez

Requirement: I want/need to do this using hosted feature services in ArcGIS Online

I'm just trying to do something like this with just one REST API call:

<SPAN class="keyword token">SELECT</SPAN> table1<SPAN class="punctuation token">.</SPAN><SPAN class="operator token">*</SPAN>
<SPAN class="keyword token">FROM</SPAN>   table1<SPAN class="punctuation token">,</SPAN> table2
<SPAN class="keyword token">WHERE</SPAN>  table1<SPAN class="punctuation token">.</SPAN>primary_key <SPAN class="operator token">=</SPAN> table2<SPAN class="punctuation token">.</SPAN>foreign_key
       <SPAN class="operator token">AND</SPAN> table1<SPAN class="punctuation token">.</SPAN>attr <SPAN class="operator token">=</SPAN> <SPAN class="string token">'Value1'</SPAN> 
       <SPAN class="operator token">AND</SPAN> table2<SPAN class="punctuation token">.</SPAN>attr <SPAN class="operator token">=</SPAN> <SPAN class="string token">'Value2'</SPAN> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Now I'm going to explain the approach I have been following and the issue I'm facing, but maybe the approach is wrong.

Steps:

1. I created a file geodatabase in ArcGIS Pro with two Feature Classes (table1 & table2)
2. I created a relationship class where: table1.primary_key = table2.foreign_key
3. I published it in ArcGIS Online

Then I tried to do a similar query to this one using the query related records end point from the REST API but I couldn't:

• First, I noticed that it is not possible to set the objectIds param to "*" (which I don't quite understand why) <- but I could manage it by setting all the objectIds manually.
• But the real problem I found was that in the definitionExpression param I could only use attributes from one table/layer (the related one) but not from both of them.

So, I'm not sure if the only way to solve this is programmatically by:

1. First query one table to get only the OBJECTIDs that meet all the criteria established to this table
2. And then use the query related records setting the objectIds to those I recovered in step 1 and set the definitionExpression to add the pending criteria (the one to filter also the data within the related table).

Is this the way to solve what I'm trying to achieve or there's some way to do it with just one API request?.

Best regards,

Raul

BTW: I also tried doing a JOIN in memory and then selecting features by attributes and it worked in ArcGIS Pro, but I couldn't publish that on ArcGIS Online

Raul_Jimenez

It can be solved by creating a Hosted Feature Layer View using the Join feature tool in Online.

BTW: this analysis tool takes no credits for analysis and storage when creating a Hosted View.

This way you create a hosted feature layer view that joins the two tables together and returns a new hosted layer (a new item). Things to bear in mind:

• As data changes in your source tables the data accessed via the view will always be up to date
• As you will have the fields from both tables available you will be also able to filter by any combination of fields

Note: in order to create a hosted layer view you do need to own both tables.

Thanks to Jeremy Bartley‌ for your help!,

Raúl