I currently have a Nested Query that contains 3 levels:
|- ParentQuery
|-- ChildQuery
|— SubChild Query
I first used what is found in the Scripting Data Source Doc to pull 5 different datasets and combine them into 1 data source kindof like what Kathy did here .
I then added the SubChild Query to my nested query and tried tailoring my script to pull the SubChild data using the same (.getNestedQueryResults()) inside of a nested for loop. However, I feel like I am missing something here. I am not very well versed in python but I know Kathy mentioned that this function returns a ‘map’ and I was thinking this is where my knowledge is breakingdown on the subject.
I am getting an Error at line 17 “sub_nested = children[‘NestedQuery_Child’].getNestedQueryResults()” of:
INFO | jvm 1 | 2020/11/30 18:33:44 | com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last):
INFO | jvm 1 | 2020/11/30 18:33:44 | File “function:updateData”, line 17, in updateData
INFO | jvm 1 | 2020/11/30 18:33:44 | TypeError: ‘com.inductiveautomation.ignition.common.BasicDataset’ object is unsubscriptable
Below is my code to access the SubChild Query:
#This code takes all the resultant subchildren and consolidates them into 1 dataset per child....
nested = data['NestedQuery_Parent'].getNestedQueryResults() # Gets results from our parent query
childQuery = nested['NestedQuery_Child'] # Gets the subquery we want -- there can be more than one
childHeader = ['accesschildCol1']
i=0
for child in childQuery:
i=i+1
children = child.getCoreResults() # children is a dataset
sub_nested = children['NestedQuery_Child'].getNestedQueryResults()
sub_childQuery = sub_nested['NestedQuery_subChild']
sub_childHeader = ['accessCol1', 'accessCol2', 'accessCol3']
sub_alteredDataset = []
for sub_child in sub_childQuery:
sub_children = sub_child.getCoreResults() # children is a dataset
for row in range(sub_children.rowCount):
val_accessCol1 = children.getValueAt(row,'Col1')
val_accessCol2 = children.getValueAt(row,'Col2')
val_accessCol3 = children.getValueAt(row,'Col3')
sub_alteredDataset.append([val_accessCol1,val_accessCol2,val_accessCol3])
# convert the pydataset to a standard dataset
sub_alteredDataset = system.dataset.toDataSet(sub_childheader, sub_alteredDataset)
# create a new data source with the altered results
data['Updated NestedQuery_Child'+str(i)] = sub_alteredDataset
I belive it should be getRowCount() and not rowCount
I don’t believe you can use getValueAt() with a column name with BasicDataset. Use getColumnNames() to help get the column indexes.
.
EDIT: You can use a column name in v8.0+, according to the javadocs, but I don’t think that was backported to 7.9
for sub_child in sub_childQuery:
sub_children = sub_child.getCoreResults() # children is a dataset
colNames = list(sub_children.getColumnNames())
for row in range(sub_children.getRowCount()):
val_accessCol1 = children.getValueAt(row, colNames.index('Col1'))
val_accessCol2 = children.getValueAt(row, colNames.index('Col2'))
val_accessCol3 = children.getValueAt(row, colNames.index('Col3'))
sub_alteredDataset.append([val_accessCol1,val_accessCol2,val_accessCol3])
No, they are equivalent. Jython automatically converts methods that follow NetBeans getter/setter conventions into virtual properties. I always use rowCount.
BasicDataset has supported named columns in .getValueAt() for ages. It is the core Dataset interface that was upgraded in v8 to have some default methods that backport some of BasicDataset's functionality.
Thank you for the reply! That portion was taken almost directly from Scripting Data Source doc and seems to work fine when I have a single nested query.
I seem to be getting hung up before that portion, at the lines containing:
I figured it out. I used a lot of print statements and then looked in the wrapper.log file to see the format of what was returned.
Thank you for the help!
#Manipulated from v7.9 User Manual: Scripting Data Source: Building a New Data Source
#This code takes all the resultant subchildren and consolidates them into 1 dataset per child....
parent = data['NestedQuery_Parent']
# print parent
nested = data['NestedQuery_Parent'].getNestedQueryResults() # Gets results from our parent query
# print nested
childQuery = nested['NestedQuery_Child'] # Gets the subquery we want -- there can be more than one
# print childQuery
childHeader = ['accesschildChuteName']
i=0
for child in childQuery:
i=i+1
# print i
# print child
sub_nested = child.getNestedQueryResults()
# print sub_nested
sub_childQuery = sub_nested['NestedQuery_subChild']
# print sub_childQuery
sub_childHeader = ['accessChuteName', 'accessGoodSorts', 'accessBadSorts']
sub_alteredDataset = []
for sub_child in sub_childQuery:
sub_children = sub_child.getCoreResults() # children is a dataset
# print sub_children
for row in range(sub_children.rowCount):
val_accessCol1 = sub_children.getValueAt(row,'ChuteName')
val_accessCol2 = sub_children.getValueAt(row,'Good Sorts')
val_accessCol3 = sub_children.getValueAt(row,'Bad Sorts')
sub_alteredDataset.append([val_accessCol1,val_accessCol2,val_accessCol3])
# convert the pydataset to a standard dataset
sub_alteredDataset = system.dataset.toDataSet(sub_childHeader, sub_alteredDataset)
# create a new data source with the altered results
data['Updated NestedQuery_Child'+str(i)] = sub_alteredDataset
