LabelClass where clause with spaces in value (API v3)

[Polopolo]

Hello,

I have an issue when trying to apply a where clause to some of my LabelClass.

My first where clause is working fine: 

where: `STATUS = 'Done'`,

This one not:

where: `STATUS = 'To Do'`,

 I think it's because there is a space in my value.

I have also tried this, but it's not working:

where: `STATUS IN ('To Do')`,

In ArcMap this where clause is working fine.

Do you know how to solve this?

[LefterisKoumis]

Use double quotes and single quotes. Try:

where: "STATUS = 'To Do'"

[KenBuja]

It fails with a space in the string in this sample.

This works (inserted at line 70): labelClass.where = "STATE_NAME = 'Virginia'"

This doesn't: labelClass.where = "STATE_NAME = 'West Virginia'"

[JoelBennett · Accepted solution]

This just plain won't work out of the box, but that's not to say you can't work around it. We should first note that the documentation for LabelClass.where says "Only very basic SQL is supported."  Perhaps if we better understood the qualifications for that, we could better understand why this fails.  Fortunately, we can.  The logic for evaluating the where clause of a LabelClass is found in the implementation of the LabelLayer class.  Basically, it comes down to a private (and therefore undocumented) method called "_isWhere", which I have reproduced here:

_isWhere: function(d, l) {
	try {
		if (!d)
			return !0;
		if (d) {
			var t = d.split(" ");
			if (3 === t.length)
				return this._sqlEquation(l[this._removeQuotes(t[0])], t[1], this._removeQuotes(t[2]));
			if (7 === t.length) {
				var E = this._sqlEquation(l[this._removeQuotes(t[0])], t[1], this._removeQuotes(t[2]))
				  , G = t[3]
				  , n = this._sqlEquation(l[this._removeQuotes(t[4])], t[5], this._removeQuotes(t[6]));
				switch (G) {
				case "AND":
					return E && n;
				case "OR":
					return E || n
				}
			}
		}
		return !1
	} catch (F) {
		console.log("Error.: can't parse \x3d " + d)
	}
}

With this, we can better understand what they mean by only basic SQL being supported.  The problem happens because they split the where clause on spaces in order to evaluate it, and this implementation expects that the value portion of the expression will not have any spaces in it.  This is why it fails out of the box for you, and there's really no way we can manipulate the expression to make it succeed.  Instead, if we want to make this work, we'll have to alter the implementation.  Fortunately, that's not terribly hard.

Basically, within the first "require" statement of your application, you'll want to include a reference to "esri/layers/LabelLayer", and then you'll want to override the definition of the _isWhere function.  I will use the sample referred to by @KenBuja as an example.

First, we add the reference in the call to "require" (note changes on lines 5 and 14).

      require([
        "esri/map", 
        "esri/geometry/Extent",
        "esri/layers/FeatureLayer",
        "esri/layers/LabelLayer", //added line
        "esri/symbols/SimpleLineSymbol",
        "esri/symbols/SimpleFillSymbol",
        "esri/symbols/TextSymbol",
        "esri/renderers/SimpleRenderer",
        "esri/layers/LabelClass",
        "esri/Color",
         
        "dojo/domReady!"
      ], function(Map, Extent, FeatureLayer, LabelLayer, //modified line
                  SimpleLineSymbol, SimpleFillSymbol, 
                  TextSymbol, SimpleRenderer, LabelClass, Color) 
      {

Immediately after this, we'll add our override of the _isWhere function:

        LabelLayer.prototype._isWhere = function(d, l) {
            try {
                if (!d)
                    return !0;
                if (d) {
                    var t = d.split(" ");
                    if (3 === t.length)
                        return this._sqlEquation(l[this._removeQuotes(t[0])], t[1], this._removeQuotes(this._replaceSpaces(t[2])));
                    if (7 === t.length) {
                        var E = this._sqlEquation(l[this._removeQuotes(t[0])], t[1], this._removeQuotes(t[2]))
                          , G = t[3]
                          , n = this._sqlEquation(l[this._removeQuotes(t[4])], t[5], this._removeQuotes(t[6]));
                        switch (G) {
                        case "AND":
                            return E && n;
                        case "OR":
                            return E || n
                        }
                    }
                }
                return !1
            } catch (F) {
                console.log("Error.: can't parse \x3d " + d)
            }
        };

You will see it is exactly the same as the default implementation, except that I have added a call to a function "replaceSpaces" to the third argument on line 8.  However, this function does not exist, so we will add it immediately afterwards:

        LabelLayer.prototype._replaceSpaces = function(a) {
          return a.replace(/ /g, " ");
        };

You'll see this function replaces any instances of the arbitrary placeholder " " with a space.  With this in place, we can now replace any spaces in our where clause value with that placeholder, and things will work.  In this case, as with Ken's suggestion, we could try:

labelClass.where = "STATE_NAME = 'West Virginia'";

And it now works as expected.  For clarity then, I've reproduced the entire contents within the script tag of the sample below.  Modifications/additions are on lines 7, 16, 20-47, and 85.

      var map;
    
      require([
        "esri/map", 
        "esri/geometry/Extent",
        "esri/layers/FeatureLayer",
        "esri/layers/LabelLayer",
        "esri/symbols/SimpleLineSymbol",
        "esri/symbols/SimpleFillSymbol",
        "esri/symbols/TextSymbol",
        "esri/renderers/SimpleRenderer",
        "esri/layers/LabelClass",
        "esri/Color",
         
        "dojo/domReady!"
      ], function(Map, Extent, FeatureLayer, LabelLayer,
                  SimpleLineSymbol, SimpleFillSymbol, 
                  TextSymbol, SimpleRenderer, LabelClass, Color) 
      {
        LabelLayer.prototype._isWhere = function(d, l) {
            try {
                if (!d)
                    return !0;
                if (d) {
                    var t = d.split(" ");
                    if (3 === t.length)
                        return this._sqlEquation(l[this._removeQuotes(t[0])], t[1], this._removeQuotes(this._replaceSpaces(t[2])));
                    if (7 === t.length) {
                        var E = this._sqlEquation(l[this._removeQuotes(t[0])], t[1], this._removeQuotes(t[2]))
                          , G = t[3]
                          , n = this._sqlEquation(l[this._removeQuotes(t[4])], t[5], this._removeQuotes(t[6]));
                        switch (G) {
                        case "AND":
                            return E && n;
                        case "OR":
                            return E || n
                        }
                    }
                }
                return !1
            } catch (F) {
                console.log("Error.: can't parse \x3d " + d)
            }
        };
        LabelLayer.prototype._replaceSpaces = function(a) {
          return a.replace(/ /g, " ");
        };
        // load the map centered on the United States
        var bbox = new Extent({"xmin": -1940058, "ymin": -814715, "xmax": 1683105, "ymax": 1446096, "spatialReference": {"wkid": 102003}});
      
        //create the map and set the extent, making sure to "showLabels"
        map = new Map("map", {
          extent: bbox,
          showLabels : true //very important that this must be set to true!   
        });

        // create a renderer for the states layer to override default symbology
        var statesColor = new Color("#666");
        var statesLine = new SimpleLineSymbol("solid", statesColor, 1.5);
        var statesSymbol = new SimpleFillSymbol("solid", statesLine, null);
        var statesRenderer = new SimpleRenderer(statesSymbol);
         
        // create the feature layer to render and label
        var statesUrl = "https://sampleserver6.arcgisonline.com/arcgis/rest/services/Census/MapServer/3";
        var states = new FeatureLayer(statesUrl, {
          id: "states",
          outFields: ["*"]
        });
        states.setRenderer(statesRenderer);


        // create a text symbol to define the style of labels
        var statesLabel = new TextSymbol().setColor(statesColor);
        statesLabel.font.setSize("14pt");
        statesLabel.font.setFamily("arial");

        //this is the very least of what should be set within the JSON  
        var json = {
          "labelExpressionInfo": {"value": "{STATE_NAME}"}
        };

        //create instance of LabelClass (note: multiple LabelClasses can be passed in as an array)
        var labelClass = new LabelClass(json);
        labelClass.symbol = statesLabel; // symbol also can be set in LabelClass' json
        labelClass.where = "STATE_NAME = 'West Virginia'";
        states.setLabelingInfo([ labelClass ]);
        map.addLayer(states);
    
      });

[Polopolo]

Thank you for your help Joel! It's working now

Where can I find the implementation of the API method so next time I will search by myself?
I've found this repo but it is minified https://github.com/Esri/arcgis-js-api/blob/3.40.0/layers/LabelClass.js

[JoelBennett]

You can download a copy of the API as described at the bottom of this page.  You can also view the source code in your browser's developer tools (typically F12), most of which now provide tools for "beautifying" the code (i.e. formatting it to make it readable).