While updating query on query layer in ArcMap is nightmare process, it seems with ArcGIS Pro this could possibly be better using Python CIM Access.
I have tried following code and it works in the way I am able to get / replace SQL query on layer:
aprx = arcpy.mp.ArcGISProject('current') am = aprx.activeMap lyr = am.listLayers() # Picks first layer in active map lyr_cim = lyr.getDefinition('V2') # Reads CIM definition query = lyr_cim.featureTable.dataConnection.sqlQuery # Reads SQL Query query += ' WHERE GEOM.STX > 0' # Example of query update lyr_cim.featureTable.dataConnection.sqlQuery = query # Set query in definition lyr.setDefinition(lyr_cim) # Update definition on layer
The problem is at that moment all features from layer disappear and I get red exclamation symbol next to layer name with tool tip 'Click to repair layer's data source.'
There is nothing wrong with the query or data source, ArcGIS Pro just thinks for some reason there is. If you click through user interface of layer query properties, the updated query is there, and is correct. You just must pretend edit (e.g. delete space and add space) to be able to manually click on 'Validate' button, and 'Next', and 'Finish' button in the next window.
How do I tell ArcGIS Pro programmatically that the layer is alright?
This is a workaround in ArcGIS Pro to be able to programmatically filter features for export to ArcGIS Online. Filtering by extent on layer does not work and sends all data to AOL, filtering by definition query fails too with error - seems publishing can't deal with definition query containing underlying DB methods like STEnvelope, STCentroid etc. Ultimately, what I want is to find some way to be able to clip layers to current map extent on the fly for publishing to ArcGIS Online