1

I'm trying to query some data using ESRI JS API version 4.0. The data table that looks like a longer version of this:

+-------------------------------------+
| Object_ID | Item_ty_co | Serv_grade |
+-----------+------------+------------+
|     1     |    "MH"    |     5      |
|     2     |    "MH"    |     3      |
|     3     |    "IN"    |     3      |
+-------------------------------------+

I am trying to group this data based on the item_ty_co and Serv_grade fields, and count the number of records in each group.

In regular SQL, my query looks like:

SELECT Item_ty_co, Serv_grade ,count(Object_ID) FROM public."pointAssets"
GROUP BY Item_ty_co ,Serv_grade 
HAVING ITEM_TY_CO not in ('PN','GN','RN','CN')

which gives me exactly the result I want. However, I want to recreate this in ESRI JS API by querying the layerview like this:

const query = layerView.layer.createQuery();      
query.outStatistics = [
   {
        onStatisticField: "Object_ID",
        outStatisticFieldName: "numberOfAssets",
        statisticType: "count"
   },
];
query.groupByFieldsForStatistics = ["Item_ty_co ","Serv_grade" ];
query.having = "ITEM_TY_CO NOT IN ('PN','GN','RN','CN')"

However, I'm having two problems here.

  1. It seems like the API just ignores any columns in the groupByFieldsForStatistics array after the first element, and doesn't use them for the groupby. When I switch the items around, it just uses the first (0th) element.
  2. query.having does not filter out the groups that I specify.

So the output for the above is an array of objects like this, with one record for the unique ITEM_TY_CO's:

0: Object { numberOfAssets: 4176, ITEM_TY_CO: "PN" }
1: Object { numberOfAssets: 4662, ITEM_TY_CO: "MH" }
2: Object { numberOfAssets: 5472, ITEM_TY_CO: "CN" }

I don't know where my mistakes are. I can write workarounds, but I'd really like a clean solution.

Querying the layer in the ArcGIS REST Services Directory interface with exactly the same parameters works perfectly. So I'm not sure why it doesn't work in the JS API. Also, the layer "supportsHavingClause" is set to true. ArcGIS REST Services Directory

2 Answers 2

1

I found the issue a while ago. I was running many different queries. I used a "base query" that all queries were cloned from (using .clone), and I forgot to add the "outStatistics" to this cloned query.

0

According to the documentation, you have to be sure your service supports the having clause. That requires an ArcGIS Server service 10.6.1 or greater.

Also the having property for the query task was added in the version 4.9 of the ArcGIS API for JavaScript. Be sure you are using a 4.9 version or higher.

Could one of those be your problem?

3
  • In the service URL, "supportsHavingClause" : true, so I don't think that that is the problem unfortunately... In addition, when I query the layer from the ArcGIS REST Services Directory, with exactly the same parameters, it works perfectly (see my edit)
    – Eoin
    Commented Sep 27, 2019 at 8:16
  • Hi @Eoin, please check the edit answer with the addition of the JS API version. Could be that your problem?
    – Katah
    Commented Oct 8, 2019 at 8:15
  • 1
    Hi @Katah, I found my mistake, I've posted the answer. I simplified my code in the post, which made bug finding impossible for SO users. I doubt many other people will have this issue as it was just a simple programming error rather than an issue with the API itself.
    – Eoin
    Commented Nov 6, 2019 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.