topic Re: querying related tables in ArcGIS API for Flex Questions

querying related tables

eddiequinlan — Tue, 31 Jan 2012 19:14:43 GMT

Hopefully someone has a simple answer which eludes me.

I've read the documentation on feature layers, the queryRelatedFeatures, and various coding examples. The concept/coding doesn't appear to be very difficult, however, I'm doing something wrong.

I'm trying to query a feature layer (parcel layer) and from there query the related table (sales table).   The user draws a graphic on the map, and from there the results are returned. The feature layer is querying correctly. It then queries the related table and this is where the problem begins. The query relate does not fail; it's only returning the records (sales) from the first parcel number in the parcel layer (queried layer). I've tried all kinds of coding variations and can't get past this point. HELP!

sincerely,
Eddie

Here is the code.

[PHP]
private function SearchLayer(event:DrawEvent):void
{

sQuery = new Query();
sQuery.spatialRelationship = Query.SPATIAL_REL_INTERSECTS;
//sQuery.where = exp_use; //search by use code
sQuery.geometry = event.graphic.geometry; //search with geometry

my_layer.addEventListener(FeatureLayerEvent.SELECTION_COMPLETE, onComplete_FeatureLayerSelection);
//my_layer.useAMF = true;
my_layer.selectionColor = 0x08f36b;
my_layer.outFields = ["*"];

my_layer.selectFeatures(sQuery, FeatureLayer.SELECTION_NEW); // search feature layer.......
}

private function onComplete_FeatureLayerSelection(e:FeatureLayerEvent):void
{
relatedQuery.objectIds = [e.features[0].attributes.FID]; //FID number in featurelayer
if (e.features.length > 0)
{
my_layer.queryRelatedFeatures(relatedQuery, new AsyncResponder(onResult, onFault));

function onResult(relatedRecords:Object, token:Object = null):void
{
   var fset:FeatureSet = (relatedRecords[e.features[0].attributes.FID]);
   var objIds:Array = new Array();

   for each (var g:com.esri.ags.Graphic in fset.features)
   {
    objIds.push(g.attributes.SAPRICE);
    trace(g.attributes.SAPRICE);
   }

   trace(objIds);
}

function onFault(info:Object, token:Object = null):void
{
   trace("on fault issue");
}
}
else
{
Alert.show("No Records Found");
}
}
[/PHP]

Re: querying related tables

RobertScheitlin__GISP — Tue, 31 Jan 2012 20:40:29 GMT

Eddie,

The code is doing what you are telling it to do, this line of code tells it to query the related records for only the first returned graphic:

relatedQuery.objectIds = [e.features[0].attributes.FID];  //FID number in featurelayer

So you need to do a for loop on the e.features

Don't forget to click the Mark as answer check and to click the top arrow (promote) as shown below:

Re: querying related tables

eddiequinlan — Tue, 31 Jan 2012 20:47:47 GMT

Hi Robert,

As always thanx for your help. I thought the same thing, so I tried to create a loop but wasn't getting the results I expected. Obviously I coded the loop wrong. Also, I was under the impression that maybe a loop wasn't needed; that the whole point of the "queryrelatedfeatures" was to use the array of selected features to then "reselect" on the related table????

I actually have written code to do just that and not use "queryrelatedfeatures". But after viewing some code I thought it would be cleaner and easier. Do you have an example of how you would loop thru the FID?

eddie

Re: querying related tables

RobertScheitlin__GISP — Sat, 11 Dec 2021 10:30:38 GMT

Eddie,

You have to loop through to get an array of ObjectIds

                var oidArr:Array = [];
                for (var rf:int=0; rf < e.features.length; rf++){
                    oidArr.push(e.features[rf].attributes.FID);
                }
                relatedQuery.objectIds = oidArr;

Re: querying related tables

eddiequinlan — Wed, 01 Feb 2012 13:23:08 GMT

Hi Robert,

I had previously tried looping as you suggested, but I would only get one relate returned from the sale table (related table). I tried your loop and get the same results. I can see that both methods create the correct array of ID numbers. Here is my method:

var oIDs:Array = new Array();

for each(var g:com.esri.ags.Graphic in e.features)
{
oIDs.push(g.attributes.FID);
}
relatedQuery.objectIds = oIDs;
trace(oIDs); // shows array of numbers

Thanx,
Eddie

Re: querying related tables

RobertScheitlin__GISP — Wed, 01 Feb 2012 13:28:52 GMT

Eddie,

So Are you saying that each OID should bave multiple (one to many) results?

Re: querying related tables

eddiequinlan — Wed, 01 Feb 2012 13:33:24 GMT

Yes. Each Parcel Record returned from the query search on the FeatureLayer will have multiple records per Parcel Record returned from the Sale table. It's kind of frustrating, because I know this concept isn't new. I'm just missing something.

Eddie

Re: querying related tables

RobertScheitlin__GISP — Wed, 01 Feb 2012 13:40:08 GMT

Eddie,

Does the layer that you are querying for relates only have one relate defined?

Re: querying related tables

eddiequinlan — Wed, 01 Feb 2012 14:27:55 GMT

Yes, one relate built in the .mxd.

Re: querying related tables

RobertScheitlin__GISP — Wed, 01 Feb 2012 17:41:13 GMT

Eddie,

So after doing some testing trying to use your original code and filling in the blanks. I was able to get multiple relate results back from one feature with out having the loop.

Here is the app.

Re: querying related tables

eddiequinlan — Wed, 01 Feb 2012 18:53:52 GMT

Hi Robert,

Thank you so much for your time and effort. I copied the code you supplied and tried it out. I get the same results as before. After looking at the code, it's essentially what I already have. It keeps returning the first (or maybe the last) queried record from the featurelayer and then returning the associated related records from the relate table. I'm at a loss. I already wrote some code to loop thru featurelayer records and query each of those against the relate table. That seems to work fairly well. I'm tying up some loose ends on it and checking the results. I sure wish I/you could figure out why I'm not getting the queryrelate feature to work correctly. If it's of any value to anyone, I'll post my code when I'm finished. Robert, if you have anymore ideas please let me know.

Thanx again,
Eddie

Re: querying related tables

RobertScheitlin__GISP — Sat, 11 Dec 2021 10:30:41 GMT

Eddie,

So now I understand what you are looking for.

Try this then

            private function onComplete_FeatureLayerSelection(e:FeatureLayerEvent):void
            {
                var oidArr:Array = [];
                for (var rf:int=0; rf < e.features.length; rf++){
                    oidArr.push(e.features[rf].attributes.OBJECTID);
                }
                relatedQuery.objectIds = oidArr;
                trace(oidArr); // shows array of numbers
                //relatedQuery.objectIds = [e.features[0].attributes.OBJECTID];  //FID number in featurelayer
                if (e.features.length > 0){
                    my_layer.queryRelatedFeatures(relatedQuery, new AsyncResponder(onResult, onFault));
                    
                    function onResult(relatedRecords:Object, token:Object = null):void
                    {
                        for (var rr:int = 0; rr < e.features.length; rr++){
                            var fset:FeatureSet = (relatedRecords[e.features[rr].attributes.OBJECTID]);
                            var objIds:Array = new Array();
                            if(fset){
                                for each (var g:com.esri.ags.Graphic in fset.features)
                                {
                                    objIds.push(g.attributes.Local_ID);
                                    trace(g.attributes.Local_ID);                            
                                }
                                
                                trace(objIds);
                            }
                        }
                        
                    }
                    
                    function onFault(info:Object, token:Object = null):void
                    {
                        trace("on fault issue");
                    }
                }
                else{
                    Alert.show("No Records Found");
                }
            }

Re: querying related tables

eddiequinlan — Wed, 01 Feb 2012 19:53:09 GMT

Hi Robert,

That did it!!! The problem was this part of the loop......

var oidArr:Array = [];
for (var rf:int=0; rf < e.features.length; rf++)
{
oidArr.push(e.features[rf].attributes.FID);
}
relatedQuery.objectIds = oidArr;

I know I had tried some variation of this, but I think I accidentally was setting the relatedQuery.objectIds inside the loop... doh! Anyway, it's good to have the forum and people like yourself to review simple issues such as this one. Thank you again.

If anyone is interested, since I have both methods working; it appears that the speed of returning the records is about the same. However, the relatedQuery method code is shorter, standardized, and easier to follow. Also, my two loops bail-out occassionally. I haven't figured out why. It seems to happen when the spatial select on the feature layer is fairly large. At this point, I haven't been able to replicate the failure.

Thanx Robert.
Sincerely,
Eddie

Re: querying related tables

RobertScheitlin__GISP — Wed, 01 Feb 2012 20:01:39 GMT

Eddie,

Glad you got it working. Don't forget to click the Mark as answer check and to click the top arrow (promote) as shown below:

Re: querying related tables

eddiequinlan — Wed, 01 Feb 2012 20:05:06 GMT

never really paid attention to what those icons on the right were for..............

Re: querying related tables

eddiequinlan — Fri, 03 Feb 2012 15:53:06 GMT

I have another question for anyone.......

Once the query relate has returned the records from the query related table, is there an efficient way to shade only those records on the map?

If I only use the queryrelatedfeatures it shades all the initial records from the featurelayer. I've taken/created an array from the returned records and then Query() each one of those records to "shade" the map. However, this looping is time consuming and not efficient.

Sincerely,
Eddie

Re: querying related tables

RobertScheitlin__GISP — Fri, 03 Feb 2012 20:45:58 GMT

Eddie,

So are you wanting to combine all the OIDs into one query? If so then all you do is this SQL Statement in the querys where attribute:

OBJECTID IN (1,2,3,4,5,6,7,8,9,10)

Re: querying related tables

eddiequinlan — Fri, 03 Feb 2012 20:53:48 GMT

Hi Robert,

Yea thats what I did, but then my sql query looks something like:

where attribute = 1 or attribute = 3 or attribute = 100 etc..... I may end up with a 1000 or's

The other option I've done is to query each record in a loop. Either way, they are both slow when I get more than a 100 records. The speed between the two seems to be about the same.

Robert thanx for responding,
Eddie