How to join table to a feature layer?

[mikaël]

I have a feature layer "assets" (points) that contains a field "categories_id" which is populated by IDs that refer to the "id" field of the "categories" table (no spatial data). There is another field in this second table which contains a "name" string field. The "assets.categories_id" field is related to the "categories.id".

I would like to count how many different categories (categories_id) exist in the "asset" table and get the name from the "categories" tables.

asset table:

id	site_id	categories_id	info
1	1	1	text1
2	1	1	text2
3	1	2	text3

categories table:

id	name
1	category 1
2	category 2
3	category 3
4	category 4

I realize I can use the "group_by_fields_for_statistics" and "out_statistics" parameters from the FeatureLayer.query function to get the count of every assets.categories_id, but I also need to get the name. What is the best way to accomplish this? Get the stats then do a join?

As a side question: I also wish to do a similar operation with two feature layers (sites and assets). Is there a way to join features based on field value (a basic SQL JOIN) rather than the spatial_relationship (as described here) ? Anyway to do that?

Thank you!

[mikaël]

I just found this arcgis.geoanalytics.summarize_data module. Gonna play around with it, see if it meets my needs.

[mikaël · Accepted solution]

I can't seem to get what I want from the API. I can't seem to figure it out. How can something as simple as joining tables or layers and grouping them to get a count per group is so complicated.

Plus, the documentation on the API is really poor, with lots of errors.

I'm just gonna query the tables and layers and do the operations locally, but it's too bad, I wished I could've done it directly on the data and return the values I wanted.