You can accomplish this, but it requires a bit of setup and understanding of how things work. Let me give you an example.
Suppose I want to join Feature Layer A to Table B. I know the field I want to join on is named "Activity," and I want to perform a Left Join.
Begin with necessary imports, initial setup:
from arcgis.gis import GIS
from arcgis.features import FeatureLayer, Table, FeatureLayerCollection
gis = GIS("https://www.arcgis.com", username="hari", password="seldon")
fl_url = "https://services3.arcgis.com/xxxxxxxxxxx/arcgis/rest/services/pntLayer/FeatureServer/0"
tbl_url = "https://services3.arcgis.com/xxxxxxxxxxx/arcgis/rest/services/TableToJoin/FeatureServer/0"
fl = FeatureLayer(fl_url, gis)
tbl = Table(tbl_url, gis)
Next, you'll want to add an index on the join field to both services:
index_to_add = {"indexes":[
{
"name": "Activity_Index",
"fields": "Activity",
"isUnique": False,
"isAscending": True,
"description": "Activity_Index"
}
]}
fl.manager.add_to_definition(index_to_add)
tbl.manager.add_to_definition(index_to_add)
Create a blank view service, initialize a FeatureLayerCollection from the result for later use:
view_service = gis.content.create_service(name="joined_view", is_view=True)
view_flc = FeatureLayerCollection.fromitem(view_service )
Now, you need to think about which fields you want from the source feature layer and source table:
sourceFeatureLayerFields = [
{
"name": "Activity",
"alias": "Activity",
"source": "Activity"
},
{
"name": "Description",
"alias": "Description",
"source": "Description"
},
{
"name": "StartDate",
"alias": "StartDate",
"source": "StartDate"
},
{
"name": "EndDate",
"alias": "EndDate",
"source": "EndDate"
}
]
sourceTableFields = [
{
"name": "Note",
"alias": "Note",
"source": "Note"
}
]
This is where the magic happens. We use some of the info above to create a definition we can use to end up with our desired joined view:
field_to_join_on = "Activity"
view_lyr_name = "sampleJoinedView"
definition_to_add = {
"layers": [
{
"name": view_lyr_name,
"displayField": "",
"description": "AttributeJoin",
"adminLayerInfo": {
"viewLayerDefinition": {
"table": {
"name": "sampleJoinedView",
"sourceServiceName": fl.properties.name,
"sourceLayerId": 0,
"sourceLayerFields": sourceFeatureLayerFields,
"relatedTables": [
{
"name": "testjoin",
"sourceServiceName": tbl.properties.name,
"sourceLayerId": 0,
"sourceLayerFields": sourceTableFields,
"type": "LEFT",
"parentKeyFields": [
field_to_join_on
],
"keyFields": [
field_to_join_on
],
"topFilter": {
"groupByFields": field_to_join_on,
"orderByFields": "OBJECTID ASC",
"topCount": 1
}
}
],
"materialized": False
}
},
"geometryField": {
"name": f"{view_lyr_name}.Shape"
}
}
}
]
}
view_flc.manager.add_to_definition(definition_to_add)
Put it all together and we have:
from arcgis.gis import GIS
from arcgis.features import FeatureLayer, Table, FeatureLayerCollection
gis = GIS("https://www.arcgis.com", username="hari", password="seldon")
fl_url = "https://services3.arcgis.com/xxxxxxxxxxx/arcgis/rest/services/pntLayer/FeatureServer/0"
tbl_url = "https://services3.arcgis.com/xxxxxxxxxxx/arcgis/rest/services/TableToJoin/FeatureServer/0"
fl = FeatureLayer(fl_url, gis)
tbl = Table(tbl_url, gis)
index_to_add = {"indexes":[
{
"name": "Activity_Index",
"fields": "Activity",
"isUnique": False,
"isAscending": True,
"description": "Activity_Index"
}
]}
fl.manager.add_to_definition(index_to_add)
tbl.manager.add_to_definition(index_to_add)
view_service = gis.content.create_service(name="joined_view", is_view=True)
view_flc = FeatureLayerCollection.fromitem(view_service )
sourceFeatureLayerFields = [
{
"name": "Activity",
"alias": "Activity",
"source": "Activity"
},
{
"name": "Description",
"alias": "Description",
"source": "Description"
},
{
"name": "StartDate",
"alias": "StartDate",
"source": "StartDate"
},
{
"name": "EndDate",
"alias": "EndDate",
"source": "EndDate"
}
]
sourceTableFields = [
{
"name": "Note",
"alias": "Note",
"source": "Note"
}
]
field_to_join_on = "Activity"
view_lyr_name = "sampleJoinedView"
definition_to_add = {
"layers": [
{
"name": view_lyr_name,
"displayField": "",
"description": "AttributeJoin",
"adminLayerInfo": {
"viewLayerDefinition": {
"table": {
"name": "sampleJoinedView",
"sourceServiceName": fl.properties.name,
"sourceLayerId": 0,
"sourceLayerFields": sourceFeatureLayerFields,
"relatedTables": [
{
"name": "testjoin",
"sourceServiceName": tbl.properties.name,
"sourceLayerId": 0,
"sourceLayerFields": sourceTableFields,
"type": "LEFT",
"parentKeyFields": [
field_to_join_on
],
"keyFields": [
field_to_join_on
],
"topFilter": {
"groupByFields": field_to_join_on,
"orderByFields": "OBJECTID ASC",
"topCount": 1
}
}
],
"materialized": False
}
},
"geometryField": {
"name": f"{view_lyr_name}.Shape"
}
}
}
]
}
view_flc.manager.add_to_definition(definition_to_add)
There are other options you can play around with in the view layer definition, but this should give you a good starting point. To get a better idea of what your definition needs to look like, you could use the UI to create the exact view you want and capture the network traffic right after you click the button to create the joined view. The call you need to pay most attention to is "addToDefinition".
Hope this helps you and a lot of other people.
