Valid syntax for "IN" where clause for Query, using a list of county names

TracySchloss · ‎09-05-2014

Looking at my rest endpoint, I can enter a where clause of COUNTYNAME IN ('Dent', 'Holt'). I'd like to let the user enter a list of county names in an input field and parse that information into a where clause for a Query. I assume if I can run a query from the endpoint, I ought to be able to do the same thing programmatically as long as I can get my syntax correct on my where clause.

The user has an input text field and they should enter county names separated by commas. Once they click a button, a where clause is created with all the counties specified. The queryTask doesn't execute because my query syntax is wrong.

function checkCountyInput() {
  var countyCheck = registry.byId('countySel').get("value");
 console.log("countyCheck = " + countyCheck);
  var countyArray = countyCheck.split(",");
  console.log("countyArray = " + countyArray);
  idList.length = 0;
  countyList.length = 0;
//the syntax that worked on the REST endpoint
  //COUNTYNAME IN ('Dent', 'Holt') 
  var whereClause = "COUNTYNAME IN (" + countyArray + ")";

  query.outSpatialReference = spatialReference;
  query.returnGeometry = false;
  query.outFields = "*";

console.log("where clause = " + whereClause);
var queryTask = new QueryTask(featureLayer.url);
queryTask.on ("error", function (err) {
  console.log("error in queryTask: " + err.message);
});
queryTask.execute(query, queryCountyResults);
}

I'm I not going about this the right way?

RobertScheitlin__GISP · ‎09-05-2014

Tracy,

Your users are just typing things like dent,holt right?

The the replace would be simple:

function checkCountyInput() { 
  var countyCheck = registry.byId('countySel').get("value"); 
console.log("countyCheck = " + countyCheck); 
  countyCheck.replace(",","','");
  //console.log("countyArray = " + countyArray); 
  idList.length = 0; 
  countyList.length = 0; 
//the syntax that worked on the REST endpoint 
  //COUNTYNAME IN ('Dent', 'Holt') 
  var whereClause = "COUNTYNAME IN ('" + countyCheck + "')"; 

  query.outSpatialReference = spatialReference; 
  query.returnGeometry = false; 
  query.outFields = "*"; 

console.log("where clause = " + whereClause); 
var queryTask = new QueryTask(featureLayer.url); 
queryTask.on ("error", function (err) { 
  console.log("error in queryTask: " + err.message); 
}); 
queryTask.execute(query, queryCountyResults); 
}

View solution in original post

RobertScheitlin__GISP · ‎09-05-2014

Tracy,

It worked in the REST End point because each of the county name was wrapped in single quotes and comma separated. Instead of splitting the string by the comma try replacing the comma with ',' and then make sure the string starts with a single quote and ends with one too.

TracySchloss · ‎09-05-2014

I get what you're saying, but I get lost trying to replace in a string where it needs to escape things like quotes.

RobertScheitlin__GISP · ‎09-05-2014

Tracy,

Your users are just typing things like dent,holt right?

The the replace would be simple:

function checkCountyInput() { 
  var countyCheck = registry.byId('countySel').get("value"); 
console.log("countyCheck = " + countyCheck); 
  countyCheck.replace(",","','");
  //console.log("countyArray = " + countyArray); 
  idList.length = 0; 
  countyList.length = 0; 
//the syntax that worked on the REST endpoint 
  //COUNTYNAME IN ('Dent', 'Holt') 
  var whereClause = "COUNTYNAME IN ('" + countyCheck + "')"; 

  query.outSpatialReference = spatialReference; 
  query.returnGeometry = false; 
  query.outFields = "*"; 

console.log("where clause = " + whereClause); 
var queryTask = new QueryTask(featureLayer.url); 
queryTask.on ("error", function (err) { 
  console.log("error in queryTask: " + err.message); 
}); 
queryTask.execute(query, queryCountyResults); 
}

TracySchloss · ‎09-05-2014

I tried a join like var countyString = countyCheck.split(', ').join("','"); and my string looks OK. I also tried your way too, again the string looks OK The console log shows whereClause2 = COUNTYNAME IN ('Cole','Dent') , which looks like the REST syntax, so I figured I was good to go.

Either method of creating the string, the queryTask is failing though. I'm getting an error:

TypeError: a.toJson is not a function

RobertScheitlin__GISP · ‎09-05-2014

Tracy,

Isn't the outfields supposed to be an array?

query.outfields = ["*"];

TracySchloss · ‎09-05-2014

You're right! That didn't fix it, though. I do think I must have an error somewhere else. My queryTask error handler isn't executing, which it normally does when I have a syntax problem on the query.

TracySchloss · ‎09-05-2014

I must be getting tired. I forgot to set my query.where to my string once I had it all formatted! Now instead of the original type function, I'm getting another one: 'undefined' is not a function.

RobertScheitlin__GISP · ‎09-05-2014

Tracy,

Here is a working sample. This sample handles if a user puts spaces between the commas.

<!DOCTYPE html>
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  <!--The viewport meta tag is used to improve the presentation and behavior of the samples  -->
  <meta name="viewport" content="initial-scale=1, maximum-scale=1,user-scalable=no">
  <title> IN Query</title>
  <script src="//js.arcgis.com/3.10/"></script>

  <script>
    require([
            "esri/tasks/query", "esri/tasks/QueryTask", "esri/config",
            "dojo/dom", "dojo/on", "dijit/registry", "dojo/parser", "dijit/form/TextBox", "dojo/domReady!"
    ], function (
            Query, QueryTask, esriConfig, dom, on, registry, parser
    ) {
              parser.parse();
      //need to reference a working proxy here, dropbox doesn't allow you to host proxies  
      //esri.config.defaults.io.proxyUrl = "http://gislap183/Proxy/proxy.ashx";
      //esri.config.defaults.io.alwaysUseProxy = true;
      //you can either explicitly include 'http' or 'https' or infer the same protocol as the running application 
      
      on(dom.byId("execute"), "click", checkCountyInput);
              
      function checkCountyInput() {   
        var countyCheck = registry.byId('countySel').get("value");
        //remove leading and trailing spaces from the array elements
        var uaList = trimArray(countyCheck.split(","));
        var uList = uaList.join("','");   
        var whereClause = "NAME IN ('" + uList + "')"; 
        var query = new Query();
        query.returnGeometry = false;   
        query.outFields = ["*"];   
        query.where = whereClause;
        console.log("where clause = " + whereClause);   
        var queryTask = new QueryTask("http://sampleserver6.arcgisonline.com/arcgis/rest/services/Census/MapServer/2");   
        queryTask.on ("error", function (err) {   
          console.log("error in queryTask: " + err.message);   
        });   
        queryTask.execute(query, showResults);   
      }

      function showResults(results) {
        dom.byId("info").innerHTML = "success";
      }

      function err(e) {
        dom.byId("info").innerHTML = e;
      }
              
      function trimArray(arr){
        for(var i=0;i<arr.length;i++)
        {
          arr = arr.replace(/^\s*/, '').replace(/\s*$/, '');
        }
        return arr;
      }
    });
  </script>
</head>
<body>
  <input id="countySel" data-dojo-type="dijit.form.TextBox" type="text" data-dojo-props="trim:true, propercase:true" style="width: 100%"><input id="execute" type="button" value="run query">
  <br />
  <br />
  <div id="info" style="padding:5px; margin:5px; background-color:#eee;">
  </div>
</body>
</html>

TracySchloss · ‎09-05-2014

In the end, I changed my error handler for my queryTask not to be as an event listener and it started working. Instead I put it at the end of the queryTask.execute(query, queryCountyResults, errorHandler); I shouldn't have had to do it that way, but it worked. I marked the answer earlier as correct, since it addressed the issue of the post.