How to query max value in map layer field

[FranklinAlexander]

I have a web application with a Query widget that is pointing to a REST service point layer. The layer is NOT in the map, and will only display the results of a query. The users will be able to perform a spatial query on the layer, but first I want to filter the data via an SQL expression or esri QueryTask so that only a subset of the data is available for the spatial query, if this makes any sense. I can't do a definition query in the mxd because the data get updated regularly, so this needs to be dynamic. Basically I want to look at all of the numbers in a particular field, find the largest number (or most recent batch) and use that number to plug in to the SQL Expression in the TaskSetting.js file (or if there is an SQL property that would do the same thing at the time of the query). This way, when the user performs the spatial query, they will only be querying the latest batch numbers and not the entire dataset. I tried using  QueryTask, but this returns feature sets, not the value, and I can't seem to figure out if there is a way to set the where statement to filter out all features except the ones with the highest value in the "Batch" field. I can hard code the expression with the correct value (see line 19), but then I have to update the value every time the data is updated on the server!

_getWhereInfo: function(){
        var result = {
          status: 0,
          where: ""
        };
        if(this.askForValues){
          var newExpr = this.filterParams.getFilterExpr();
          var validExpr = newExpr && typeof newExpr === 'string';
          if(validExpr){
            result.status = 1;
            result.where = newExpr;
          }else{
            result.status = -1;
            result.where = null;
          }
        }else{
          result.status = 1;
          //result.where = this.currentAttrs.config.filter.expr;
          result.where = "Batch = 20"; //value hard coded
          //console.log("where expression ", result.where);
        }
        if(result.status === 1 && !result.where){
          result.where = "1=1";
        }
        return result;
},‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

 I tried using "Batch = Max(Batch)" and a few variations, but no luck so far! Maybe I am going about this all wrong?

[RobertScheitlin__GISP · Accepted solution]

Franklin,

   In the jimu folder there is statisticsUtils.js that will allow you to get statistics from a field like MAX. Look at the getStatisticsResult function and there it will show you the properties of the params object it is expecting.

[FranklinAlexander]

Thanks Robert,

I was able to use the statisticsUtils to get the max value, but it takes a long time to resolve. I know I have to use the Deferred() object to resolve the QueryTask before building the where expression (I tried doing this in the _getWhereInfo function in TaskSetting.js), but could never figure out how to get it to work. Just kept returning a promise instead of the value. I ended up getting it to work by cheating and placing the TaskQuery function in the main Widget.js file so it would get called sooner, I then returned a global variable with the value, so I could use it in the where statement. I know it's a hack, but I will have to keep working on the correct way to do this. 

This is the function I placed in the Widget.js file: 

_getBatchNumber: function() {
          var sitesUrl = "https://ocean.floridamarine.org/arcgis/rest/services/Projects_FWC/Florida_Reef_Resiliency_Program/MapServer/0";
          var queryTask = new QueryTask(sitesUrl);
          var batchQuery = new Query();
          var statDef = new StatisticDefinition();
          statDef.statisticType = "max";
          statDef.onStatisticField = "Batch";
          statDef.outStatisticsFieldName = "CurrentBatch";
          batchQuery.where = "1=1";
          batchQuery.outFields = ["Batch"];
          batchQuery.outStatistics = [statDef];
          batchQuery.returnGeometry = false;
          queryTask.execute(batchQuery).then(function(result) {
              currentBatch = result.features[0].attributes.MAX_Batch;
              //console.log("Max batch value: ", this.currentBatch);
              return window.currentBatch;
          });
},‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

...and from the TaskSetting.js file:

_getWhereInfo: function(){
        var batchNum = window.currentBatch;
        var result = {
          status: 0,
          where: ""
        };
        if(this.askForValues){
          var newExpr = this.filterParams.getFilterExpr();
          var validExpr = newExpr && typeof newExpr === 'string';
          if(validExpr){
            result.status = 1;
            result.where = newExpr;
          }else{
            result.status = -1;
            result.where = null;
          }
        }else{
          result.status = 1;
          //result.where = this.currentAttrs.config.filter.expr;
          var whereExpr = "Batch = " + batchNum;
          console.log("where expression: ", whereExpr);
          result.where = whereExpr;
        }
        if(result.status === 1 && !result.where){
          result.where = "1=1";
          //return result;
        }
        return result;
},‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Thanks!!