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:
<SPAN class="">Object_ID</SPAN><SPAN class=""> </SPAN><SPAN class=""></SPAN> | <SPAN class="">Item_ty_co</SPAN><SPAN class=""> </SPAN><SPAN class=""></SPAN> | <SPAN class="">Serv_grade</SPAN><SPAN class=""> </SPAN><SPAN class=""></SPAN> |
|---|
| 1 | "MH" | 5 |
| 2 | "MH" | 3 |
| 3 | "IN" | 3 |
| 4 | "IN" | 2 |
<SPAN class=""></SPAN><SPAN class=""></SPAN><SPAN class=""></SPAN><SPAN class=""></SPAN><SPAN class=""><BR /></SPAN>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:
<SPAN class="">SELECT </SPAN><SPAN class="">Item_ty_co</SPAN><SPAN class="">,</SPAN><SPAN class=""> </SPAN><SPAN class="">Serv_grade</SPAN><SPAN class=""> </SPAN><SPAN class="">,</SPAN><SPAN class="">count</SPAN><SPAN class="">(</SPAN><SPAN class="">Object_ID</SPAN><SPAN class="">)</SPAN><SPAN class=""> FROM </SPAN><SPAN class="">public</SPAN><SPAN class="">.</SPAN><SPAN class="">"pointAssets"
</SPAN><SPAN class="">GROUP BY </SPAN><SPAN class="">Item_ty_co</SPAN><SPAN class=""> </SPAN><SPAN class="">,</SPAN><SPAN class="">Serv_grade</SPAN><SPAN class="">
HAVING ITEM_TY_CO not in </SPAN><SPAN class="">(</SPAN><SPAN class="">'PN'</SPAN><SPAN class="">,</SPAN><SPAN class="">'GN'</SPAN><SPAN class="">,</SPAN><SPAN class="">'RN'</SPAN><SPAN class="">,</SPAN><SPAN class="">'CN'</SPAN><SPAN class="">)</SPAN>
which gives me exactly the result I want. However, I want to recreate this in ESRI JS API by querying the layerview like this:
<SPAN class=""></SPAN><SPAN class="">const</SPAN><SPAN class=""> query </SPAN><SPAN class="">=</SPAN><SPAN class=""> layerView</SPAN><SPAN class="">.</SPAN><SPAN class="">layer</SPAN><SPAN class="">.</SPAN><SPAN class="">createQuery</SPAN><SPAN class="">();</SPAN><SPAN class="">
query</SPAN><SPAN class="">.</SPAN><SPAN class="">outStatistics </SPAN><SPAN class="">=</SPAN><SPAN class=""> </SPAN><SPAN class="">[</SPAN><SPAN class="">
</SPAN><SPAN class="">{</SPAN><SPAN class="">
onStatisticField</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">"Object_ID"</SPAN><SPAN class="">,</SPAN><SPAN class="">
outStatisticFieldName</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">"numberOfAssets"</SPAN><SPAN class="">,</SPAN><SPAN class="">
statisticType</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">"count"</SPAN><SPAN class=""> </SPAN><SPAN class="">},</SPAN><SPAN class="">];
</SPAN><SPAN class="">query</SPAN><SPAN class="">.</SPAN><SPAN class="">groupByFieldsForStatistics </SPAN><SPAN class="">=</SPAN><SPAN class=""> </SPAN><SPAN class="">[</SPAN><SPAN class="">"Item_ty_co "</SPAN><SPAN class="">,</SPAN><SPAN class="">"Serv_grade"</SPAN><SPAN class=""> </SPAN><SPAN class="">];
</SPAN><SPAN class="">query</SPAN><SPAN class="">.</SPAN><SPAN class="">having </SPAN><SPAN class="">=</SPAN><SPAN class=""> </SPAN><SPAN class="">"ITEM_TY_CO NOT IN ('PN','GN','RN','CN')"</SPAN>
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:
<SPAN class="">0</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">Object</SPAN><SPAN class=""> </SPAN><SPAN class="">{</SPAN><SPAN class=""> numberOfAssets</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">4176</SPAN><SPAN class="">,</SPAN><SPAN class=""> ITEM_TY_CO</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">"PN"</SPAN><SPAN class=""> </SPAN><SPAN class="">}
</SPAN><SPAN class="">1</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">Object</SPAN><SPAN class=""> </SPAN><SPAN class="">{</SPAN><SPAN class=""> numberOfAssets</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">4662</SPAN><SPAN class="">,</SPAN><SPAN class=""> ITEM_TY_CO</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">"MH"</SPAN><SPAN class=""> </SPAN><SPAN class="">}
</SPAN><SPAN class="">2</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">Object</SPAN><SPAN class=""> </SPAN><SPAN class="">{</SPAN><SPAN class=""> numberOfAssets</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">5472</SPAN><SPAN class="">,</SPAN><SPAN class=""> ITEM_TY_CO</SPAN><SPAN class="">:</SPAN><SPAN class=""> </SPAN><SPAN class="">"CN"</SPAN><SPAN class=""> </SPAN><SPAN class="">}</SPAN>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.
