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 |
4 | "IN" | 2 |
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.
- 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.
- 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. Any help/ advice would be greatly appreciated.