Data Relationship - Query Layer - Query Related Fails

SchoppMatthieu · ‎12-31-2013

'Query Related Records' from ArcGIS Server 10.1 Rest API doesn't work from my Query Layer Table.

I created a QueryLayer from tables that are not registered with the Geodatabase (Oracle 11g - ArcSDE 10.1). A Unique Identifier has been set :

[ATTACH=CONFIG]30159[/ATTACH]

The Query Layer Table has a relationship with a File Geodatabase FeatureClass :

[ATTACH=CONFIG]30160[/ATTACH]

Once published to the server, I'm querying related Records from the QueryLayerTable to the FeatureClass.
In this example I try to get Related Records for the Object ID = 3, this returns no results although I know a related Record exists in the database :

[ATTACH=CONFIG]30161[/ATTACH]

So I'm trying the other way around, I'm querying related Records from the FeatureClass to the QueryLayerTable, this time I get my "OBJECTID = 3" related record from the QueryLayerTable :

[ATTACH=CONFIG]30162[/ATTACH]

The funny thing is that once a record was returned from the FeatureClass to the QueryLayerTable, then the "Query Related Record" works the other way around from the QueryLayerTable to the FeatureClass, but just for this record.

I'm querying again Related Records from the QueryLayerTable to the FeatureClass with OBJECTID = 3, then it works :

[ATTACH=CONFIG]30163[/ATTACH]

But again, it won't work with OBJECTID = 4 although a related record exists in the database...

Does anyone reproduce this ? Is there a special configuration I missed ?
Thanks for your help.

Anonymous User · ‎12-31-2013

Original User: mboeringa2010

Looking at your second screenshot, shouldn't the "Target Table" be the FGDB "Quadrant" Feature Class, and the "Relate Table" the "CMS.%Quadrant"?

It seems you may need to change the relate...

By the way, I would avoid things like "%" characters in RDBMS database, table or field names...

Maybe this ESRI Blog article is of some help too:

http://blogs.esri.com/esri/esritrainingmatters/2009/02/26/relates-vs-relationship-classes/

Anonymous User · ‎01-01-2014

Original User: Matshopp

Thank you for looking at my problem...

Looking at your second screenshot, shouldn't the "Target Table" be the FGDB "Quadrant" Feature Class, and the "Relate Table" the "CMS.%Quadrant"?

In my case, the QueryLayer Table needs to be the source for queries. I changed it anyway for testing but the results is the same either way, related objects couldn't not be found from the QueryLayerTable to the FeatureClass table unless they were found first from the FeatureClass to the QueryLayerTable...

By the way, I would avoid things like "%" characters in RDBMS database, table or field names...

There is no special character in my DB. this "%" is automatically added by ArcGIS in the "Relation" tab of the layer properties. I neither know what it means nor where it comes from. Does anyone know ?

I had a look at you link. Although I should test, I can't use Relationship Classes because I can't modify anything in the Entreprise DB, that's why I'm using QueryLayers.

Still, this is weird that the 'Query Related' doesn't work in one way (QueryLayer Table to FeatureClass) but then it works either way, once I test it from the FeatureClass Layer to the QueryLayerTable, how could I test this in ArcMap ?

MarcoBoeringa · ‎01-02-2014

Still, this is weird that the 'Query Related' doesn't work in one way (QueryLayer Table to FeatureClass) but then it works either way, once I test it from the FeatureClass Layer to the QueryLayerTable, how could I test this in ArcMap ?

I do know the "%" character is used for in-line variable substitution in ArcGIS ModelBuilder and Python, but how that would relate to the sign showing up in the Properties dialog of the layer, other than some minor bug, I couldn't tell. It sure doesn't show up as I test it here on some dataset.

Another scenario might be that your database actually does contain % signs, either as the true fieldnames, or as the field's aliases, and that this dialog reveals either one (the true field name or the alias, more likely the alias), while you may not actually have been aware of it up to now...

Still, this is weird that the 'Query Related' doesn't work in one way (QueryLayer Table to FeatureClass) but then it works either way, once I test it from the FeatureClass Layer to the QueryLayerTable, how could I test this in ArcMap ?

I can't tell why it still isn't working. However, to test this in ArcGIS, you just need to add both the Feature Class to the ArcGIS TOC (Table Of Contents), and the table as a Query Table to your MXD document.

Then right click the layer, and establish the Relate via "Join and Relates".

Actually, you already did these steps. To test the functioning in ArcGIS, you could do the following:

- 1) Right click the Feature Class, and choose Open Attribute Table
- 2) Select a record for which you want to show related records in the other table
- 3) In the button bar of the attribute table, click the most left button, opening the menu
- 4) Choose Related Tables/YOUR RELATE NAME, this will open the related table showing the related records for the selected record you chose in step 2)

Another option to test your Relate is to use the Identify button. Click on a feature in the Feature Class, than expand the "+" signs to see the related table and related records

Anonymous User · ‎01-02-2014

Original User: Matshopp

Thank you very much,

I cannot use the "identify" tool because I need to get related elements from the QueryLayer Table to the FeatureClass. So I used the other way you mentioned :

Choose Related Tables/YOUR RELATE NAME, this will open the related table showing the related records for the selected record you chose in step 2)

It works ! So I can find related objects from the QueryLayer Table to the Feature Class in ArcMap.

The problem is that it doesn't work properly once the map document is published on ArcGIS Server. As I described earlier, I need first to query related objects from the FeatureClass to the QueryLayer in order to allow to query related objects from the QueryLayer to the FeatureClass. It seems that ArcGIS Server then store the association in memory. But I realized that the result that is returned then is wrong !

The "%" might comes from the fact that my "Quadrant" Layer is a QueryLayer with tables joins (ex : select * from CMS.table inner join CMS.OtherTable on CMS.Table.ID = CMS.OtherTable.ID)
The "%" might mean CMS."EveryTablesThatMake"Quadrant

Is ArcGIS Server designed to support "QueryAssociatedObjects" from QueryLayer ?

Anonymous User · ‎01-02-2014

Original User: mboeringa2010

I think your question is starting to become a Support incident question. I certainly can't answer these last questions. Maybe someone with more specific experience with the REST API will join the thread and give more informed answers, but you might consider contacting ESRI directly.

Maybe also the problem is with the Joins. Is the key you use in the Relate, part of the main table of the Quadrant Query Layer, or one of the joined tables?

Have you attempted to relate the FGDB Feature Class to just the one table (without joins) that contains the key for the Relate?

Anonymous User · ‎01-02-2014

Original User: Matshopp

Thank you for you answers,

I will do a few more tests and then maybe I'll contact the support.

In the main time I'm afraid that I will not be able to convince my co-workers to use ArcGIS Rest API for querying the database... We might develop proper web-services...

Anyway, thank you for your help

SchoppMatthieu · ‎01-03-2014

It seems that "Find" doesn't work either on Query Layer table.

I also found this :

http://forums.arcgis.com/threads/33117-strange-behaviour-querying-for-objects-id-a-query-layer

http://forums.arcgis.com/threads/7376-querying-a-layer-that-has-it-s-table-joined

Anonymous User · ‎01-08-2014

Original User: Matshopp

I must say, Query Layers are awesome ! I get my geometric data and complex database table relationship queries web-services through the same API that handle background queries, field aliases and other stuff. This is definitively a great improvement on AGS 10, it thoroughly enhance the possibilities.

It seems that the "FIND" method from AGS REST API only works with QueryLayers that have a geometry. I tested by publishing a mapservice with two layers made of the same data source, one had the geometry field and the other one not. Results only came back from the one that has geometry.