topic Re: returnDistinctValues not working in my query in ArcGIS JavaScript Maps SDK Questions

returnDistinctValues not working in my query

TracySchloss — Fri, 10 Dec 2021 23:05:20 GMT

I have a queryTask based on the URL of my featureLayer. This layer has 508 features in this layer. My maximum record count on my service is much higher than that, to accommodate another layer with more features in it. I'm using AGS 10.2.2 and Advanced Query Support says 'true'.

I'm using this queryTask as the source for a dropdown list, so I need to get just the distinct values from my AgencyAcronym field. My queryTask executes, but instead of the 18 distinct values for this field, I'm still getting 508 features returned. I'm having to run these through another function to remove the duplicates and I don't think I should have to do this. I've tried a where clause of '1=1', because I want to check all records, but that doesn't make a difference, I still get all 508 features.

I have my queryTask set up as:

 //populates the pick list searching state facilities by agency       
     populateBuildingList: function(){
       app.buildingAgencyList.length = 0;
       var queryTask2 = new QueryTask(app.buildingLayer.url);
       var query = new Query();
       query.where = "AgencyAcronym is not null";
       query.outFields = ["AgencyAcronym"];
       query.orderByFields = ["AgencyAcronym"];
       query.returnGeometry = false;
       query.returnDistinctValues = true;
         on(queryTask2, 'error', function(err){
           console.log('error populating building list: ' + err.error)
         });
       queryTask2.execute(query, lang.hitch(this, updateBGridHandler));
      function updateBGridHandler(results){
       console.log('results of updateBGridHandler :' + results.features.length + " records" );  //all 508 records returned. 
        arrayUtils.forEach(results.features, function(feature){
          var agencyName = feature.attributes.AgencyAcronym;
           agencyName = agencyName.replace(/^\s+|\s+$/g,'');
          app.buildingAgencyList.push(agencyName);
        });
        app.buildingAgencyList.sort();
        var sortedList = common.sortAndRemoveDuplicates(app.buildingAgencyList);  //a function I'm having to use because the values aren't unique
        var data = arrayUtils.map(sortedList, function(itm, idx){
          return {
            "id": idx,
            "name": itm,
            "value": itm
          }
        })
        var currentMemory = new Memory({
          data: data,
          idProperty: 'id'
        });
        app.agencyDropdown.set("store", currentMemory);  //a grid created earlier
        app.agencyDropdown.sort('name');
       } 
      }

Re: returnDistinctValues not working in my query

SteveCole — Wed, 26 Aug 2015 17:57:09 GMT

What permutations of your where clause have you tried?

Shouldn't it be more like this:

query.where = "AgencyAcronym <> null";

Re: returnDistinctValues not working in my query

TracySchloss — Wed, 26 Aug 2015 18:11:17 GMT

Since I want to look at all records, I used query.where = '1=1'. It doesn't make a difference. Generally if there's something very wrong with the query clause it won't execute at all.

I got that syntax from another thread. I realize it might be something different, depending on the geodatabase format. This happens to be a file geodatabase. 1=1 should do work, though, no matter the format.

Re: returnDistinctValues not working in my query

SteveCole — Wed, 26 Aug 2015 18:19:49 GMT

Consider the lone source but this discussion has a comment saying distinct isn't supported in FileGeoDbs. Maybe it needs to be in an SDE database?...

Re: returnDistinctValues not working in my query

TracySchloss — Wed, 26 Aug 2015 18:49:15 GMT

Actually in production it will be an XY event from a SQL Server business application. There's nothing in the JS API documentation that mentions the type of data source it has to be. It's happening at the service, though, because I can try a query from the REST endpoint and it doesn't work properly there either.

I guess I'll have to keep my 'removeDuplicates' function after all.

Re: returnDistinctValues not working in my query

KenBuja — Wed, 26 Aug 2015 19:02:57 GMT

Did you try the callback mentioned in this discussion? That said, what this properly added to the API, Kelly Hutchins?

Re: returnDistinctValues not working in my query

TracySchloss — Wed, 26 Aug 2015 19:38:54 GMT

No, because Kelly said it was on the list for the next release (3.11 at that writing). I'm sure it's now fixed.

I should have mentioned, if I didn't already, this is a secure service. I'm not sure if this will work, because ioArgs.url is always going to have a token on the end and it's not going to match the original url of my featureLayer.

populateBuildingList: function(){

esriRequest.setRequestPreCallback(function(ioArgs){

console.log("ioArgs.url: " + ioArgs.url);

if(ioArgs.url === "../../proxy/proxy.ashx?"+app.buildingLayer.url+"/query"){ //never resolves to true

ioArgs.content.returnDistinctValues = true;

}

return ioArgs;

});

app.buildingAgencyList.length = 0;

var queryTask2 = new QueryTask(app.buildingLayer.url);

.....

Re: returnDistinctValues not working in my query

TracySchloss — Wed, 26 Aug 2015 19:55:30 GMT

I changed it to

var urlPos = ioArgs.url.search(app.buildingLayer.url+"/query");

if (urlPos > 0) {

// if(ioArgs.url === "../../proxy/proxy.ashx?"+app.buildingLayer.url+"/query"){

ioArgs.content.returnDistinctValues = true;

}

return ioArgs;

});

At least now it's finding where I'm querying that url, but now there's no results to my query.

Re: returnDistinctValues not working in my query

KellyHutchins — Wed, 26 Aug 2015 22:35:15 GMT

Yes it was added at 3.11

Query | API Reference | ArcGIS API for JavaScript

Re: returnDistinctValues not working in my query

TracySchloss — Thu, 27 Aug 2015 13:09:38 GMT

I don't believe it is working correctly with secure services. I will make a more simple test and see if my theory is correct. It might be a bug.

Re: returnDistinctValues not working in my query

TracySchloss — Thu, 27 Aug 2015 13:58:43 GMT

I believe this is a bug. When I change my code to use a non-secured service, both orderByFields and returnDistictValues works just fine. With secure services, running through a proxy etc, neither one of these work.

Kelly Hutchinsate this?

Re: returnDistinctValues not working in my query

KellyHutchins — Thu, 27 Aug 2015 16:23:53 GMT

Tracy Schloss it looks like you are still using the workaround of setPreRequestCallback. That workaround is no longer required at version 3.11 of the api and later. Can you try using the query's returnDistinctValues and see if that resolves the issue.

Query | API Reference | ArcGIS API for JavaScript

Re: returnDistinctValues not working in my query

TracySchloss — Thu, 27 Aug 2015 16:36:18 GMT

I had a version that didn't have it, and that didn't help.

There must be something in particular about this service. I tried another service that I've had for a while, making a secure version of it. I was able to generate a list of distinct values without any problem from it. It is based on a SQL server geodatabase, where my initial service is a file geodatabase. I haven't read any limitations about that, except from an older thread that may or may not have any bearing.

My problem service doesn't return distinct values through the REST endpoint either. Of course what you can do with REST and what's in the API aren't always identical.

In the end, I just went back to my function that removed duplicates, so at least I can move forward.

Re: returnDistinctValues not working in my query

KellyHutchins — Thu, 27 Aug 2015 16:39:39 GMT

Tracy perhaps the 'problem' service doesn't support querying using things like Distinct. You can test this by checking the layer's advancedQueryCapabilities property.

Re: returnDistinctValues not working in my query

TracySchloss — Thu, 27 Aug 2015 16:50:16 GMT

I'd just been going by what I could see from the REST service, which said

Supports Advanced Queries: true

I tried var test = app.buildingLayer.advancedQueryCapabilities; and looked at that object. It is