returnDistinctValues not working in my query

3996
14
08-26-2015 09:33 AM
TracySchloss
Frequent Contributor

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');
       } 
      }  
0 Kudos
14 Replies
SteveCole
Frequent Contributor

What permutations of your where clause have you tried?

Shouldn't it be more like this:

query.where = "AgencyAcronym <> null";

0 Kudos
TracySchloss
Frequent Contributor

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.

0 Kudos
SteveCole
Frequent Contributor

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?...

0 Kudos
TracySchloss
Frequent Contributor

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. 

0 Kudos
KenBuja
MVP Esteemed Contributor

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

0 Kudos
KellyHutchins
Esri Frequent Contributor
0 Kudos
TracySchloss
Frequent Contributor

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);

.....

0 Kudos
TracySchloss
Frequent Contributor

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.

0 Kudos
TracySchloss
Frequent Contributor

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.

0 Kudos