QueryTask where clause syntax, value from an array

5333
1
Jump to solution
03-17-2015 02:10 PM
TracySchloss
Frequent Contributor

If I have an array of values, can't use these as input to a where clause?  I am allowing the user to select multiple checkboxes, pushing the values into an array as they check.  Then I want to be able to create a where clause for either a queryTask or a featureLayer.

I have an example of a where clause that looks like I can use IN, but I'm having a hard time getting the values from the array to be properly formatted.  I am using a cbTree, which is a checkbox tree.  The values from the checkboxes are getting populated into the array, I'm just not sure what to do with them next.

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=7,IE=9" />
    <meta name="viewport" content="initial-scale=1, maximum-scale=1,user-scalable=no"/>
    <title>CheckBox Tree Example</title>
       <link rel="stylesheet" href="https://js.arcgis.com/3.11/dijit/themes/claro/claro.css"/> 
        <link rel="stylesheet" href="cbtree/themes/claro/claro.css"/> 

   <script type="text/javascript">
     var dojoConfig = { 
      parseOnLoad: false,
      async:true,
        packages: [{
          "name": "cbtree",
          "location": location.pathname.replace(/\/[^/]+$/, "")+'/cbtree'
        }]
      };
    </script>
   <script type="text/javascript" src="https://js.arcgis.com/3.11/"></script>
   <script type="text/javascript">
    var popup, map, geocoder, layer, template, symbol;
    var searchList = [];
    var pathName = "https://myserver.mo.gov";
    require([
       "dojo/parser","dojo/dom-style", "dojo/dom-construct","esri/dijit/Popup","dojo/store/Memory",
       "dojo/store/Observable","cbtree/Tree", "cbtree/store/ObjectStore", "cbtree/model/ForestStoreModel",
       "cbtree/extensions/TreeStyling",
       "esri/map", "esri/layers/GraphicsLayer",
       "esri/symbols/PictureMarkerSymbol","esri/symbols/SimpleMarkerSymbol","esri/symbols/SimpleLineSymbol",
       "esri/InfoTemplate","esri/tasks/query","esri/tasks/QueryTask",
       "dojo/_base/Color", "dojo/dom","dojo/on","dijit/registry", "dojo/_base/array",
       "dijit/layout/BorderContainer","dijit/layout/TabContainer","dijit/layout/ContentPane","dijit/form/Button",
        "dijit/TitlePane",  "dojo/domReady!" ], function (
          parser,domStyle,domConstruct, Popup, Memory, Observable,Tree, ObjectStore, StoreModel, 
          TreeStyling, Map,GraphicsLayer,PictureMarkerSymbol, SimpleMarkerSymbol, 
          SimpleLineSymbol,InfoTemplate,Query,QueryTask, Color, dom,on, registry, arrayUtils) {
            parser.parse();
            
            var qTask = new QueryTask(pathName+"/arcgis/rest/services/DHSS/medicalFacility/MapServer/0");
            var searchQuery = new Query();
            searchQuery.returnGeometry = false;
            searchQuery.outFields = ["Facility", "Address", "City","Hosp_Type", "OBJECTID"];//fields you want in your grid

          var data = [
           //   { id: "Provider", name:"Provider Types", type:"header"},
              { id: "CA", name:"Critical Access", type:"parent"},
              { id: "GA", name:"General Acute Care", type:"parent"},
              { id: "LT", name:"Long Term Care", type:"parent" },              
              { id: "PSY", name:"Psychiatric", type:"parent" },
              { id: "REH", name:"Rehabilitation", type:"parent" },
              { id: "VA", name:"Veterans", type:"parent"},
              { id: "OT", name:"Other", type:"parent"}
            ];
         var store = new Memory( { data: data });
     //    var store = Observable( new Memory( { data: data }));
            var model = new StoreModel( { store: store,
                 rootLabel: "Specialty Type",
                 showRoot:false,
                 query: {type: 'parent'},
               });

          tree = new Tree( {
            model: model,
            id: "tree02",
            branchReadOnly: false,
            branchcheckBox:true,
            branchIcons: false,
            nodeIcons: false,
            leafIcons:false,
            rootNode:false
            }, "CheckboxTree" );

            tree.startup();
       // Establish listener and start the tree.
         function checkBoxClicked( item, nodeWidget, evt ) {
            var newState = nodeWidget.get("checked" );
            var label    = this.model.getLabel(item);
            if( newState ) {
              searchList.push(label);
            } else {
            }
          }
//---------------this is the section I have questions about
      function executeQuery(){
          searchList.sort();
          var sortedList = sortAndRemoveDuplicates(searchList);
          var sortedString = sortedList.split(', ').join("','");
          var whereClause = "HOSP_TYPE IN ('" + sortedString + "')";//clause isn't formated properly here
          console.log("whereClause = " + whereClause);
          query.where = whereClause;
          queryTask.execute(query,queryHandler);

      }
     function queryHandler(results){
       console.log("in queryHandler");
     }
     function sortAndRemoveDuplicates(arr) {
    arr.sort( function(a, b) { return a - b; } );
    var copy = arr.slice(0);
    arr.length = 0;


    for (var i = 0, len = copy.length; i < len; ++i) {
        if (i == 0 || copy != copy[i - 1]) {
            arr.push(copy);
        }
    }
    return arr;
}
     //event listeners
       tree.on( "checkBoxClick", checkBoxClicked );
       registry.byId('btnSearch').on('click', executeQuery);
    });
  </script>
 </head>
  <body class="claro">
    <h1 class="DemoTitle">The CheckBox Tree with Multi State CheckBoxes</h1>
    <p>
      A basic CheckBox Tree using a dojo/store Memory store (non-observable).
    </p>
    <div id="CheckboxTree">
    </div>
            <button id="btnSearch" data-dojo-type="dijit/form/Button">Search</button>
  </body>
</html>
Tags (2)
0 Kudos
1 Solution

Accepted Solutions
TracySchloss
Frequent Contributor

I got it to work. The syntax should be:

      function executeQuery(){
          searchList.sort();
          var sortedList = sortAndRemoveDuplicates(searchList);
          var sortedString = sortedList.join("','");
          var whereClause = "HOSP_TYPE IN ('" + sortedString + "')";
          console.log("whereClause = " + whereClause);
          searchQuery.where = whereClause;
          qTask.execute(searchQuery,queryHandler);
      }

View solution in original post

0 Kudos
1 Reply
TracySchloss
Frequent Contributor

I got it to work. The syntax should be:

      function executeQuery(){
          searchList.sort();
          var sortedList = sortAndRemoveDuplicates(searchList);
          var sortedString = sortedList.join("','");
          var whereClause = "HOSP_TYPE IN ('" + sortedString + "')";
          console.log("whereClause = " + whereClause);
          searchQuery.where = whereClause;
          qTask.execute(searchQuery,queryHandler);
      }
0 Kudos