I've run into an issue that's crashing my gateway...
I have a list of part ids stored in an array in a custom component prop. The ids can be in any particular order, and not sequental/incremental.
I need to run a query on a database table and order it in the order of the ids in the custom prop list.
The most basic example of the query is:
SELECT id, others
FROM table
WHERE id IN ({custom.partIds})
UNION
SELECT -3600, others
UNION
SELECT -25300, others
1st question: is it possible to order by the part Ids in the query itself?
Note: the UNIONS are dynamically added into the SQL query. These ids are "special" cases and don't actually exist as ids in the SQL table, but still need to be returned in the list. I could have also added these onto the resultset, but chose to do it in the query itself.
The resultset will come in the wrong id order, so I need to order it according to the part ids custom array, which currently I'm doing in a wildly inefficient python function which loops through all part ids and resultset rows, and essentially recreates the resultset 
If I have >~350 part Ids, the gateway crashes while running the sorting function... If I remove the sorting, it is ok.
Unless you really need to see my abomination of a function, I can post it I've posted it. But are there any more efficient ways to reorder this?
LOGGER = system.util.getLogger('Project-Scripting-shared.util.dataset')
def sortDatasetByFieldValues(dataset, fieldNames, fieldValues, DEBUG=False):
"""
Sorts a DataSet or pyDataSet by the fieldValues passed in for the fieldName in the dataset.
Notes:
- if a field value in the list doesn't exist in the dataset, an error is returned
- rows will be filtered out of the dataset if the value doesn't exist in the fieldValues
- if the fieldValues contain duplicate values, duplicates will also appear in the returned dataset
Args:
fieldNames [list] - a list of field names to order, in order or precedence
E.g. ['partId', 'part_length']
fieldValues [list of lists] - a list of lists containing the values of the field names in each list of list
E.g. [[1005,1004,1,1001,1], [None, None, 3700, None, 1500]]
"""
fna = 'sortDatasetByFieldValues(dataset={}, fieldNames={}, fieldValues={})'.format(dataset, fieldNames, fieldValues)
t = [nanoTime()]
data_sorted = []
valueFound = False
headers = list(dataset.columnNames)
for fieldValueIndex in range(len(fieldValues[0])):
valueFound = False
if DEBUG: print 'looking for ', [None if fieldValues[i][fieldValueIndex] is None else fieldValues[i][fieldValueIndex] for i in range(len(fieldValues))]
for row in range(dataset.rowCount):
if DEBUG: print ' row:', row, 'value:', [dataset.getValueAt(row, fieldName) for fieldName in fieldNames]
if all(fieldValues[i][fieldValueIndex] is None or str(dataset.getValueAt(row, fieldNames[i])) == str(fieldValues[i][fieldValueIndex]) for i in range(len(fieldNames))):
if DEBUG: print ' found fieldValue'
data_sorted.append([dataset.getValueAt(row, header) for header in headers])
# remove the row from the dataset to reduce search time!!
#dataset = system.dataset.deleteRow(dataset, row) # this isn't working... rows cannot be found if this is in
valueFound = True
break
if not valueFound:
if DEBUG: print ' NOT FOUND!'
msg = '{}\r\nField `{}` value `{}` not found in SQL table.'.format(fna, fieldNames, ','.join(map(str, [None if fieldValues[i][fieldValueIndex] is None else fieldValues[i][fieldValueIndex] for i in range(len(fieldValues))])))
LOGGER.error(msg)
raise ValueError(msg)
t.append(nanoTime())
LOGGER.trace('{}: Finished sorting. Time: {} us'.format(fna, (t[1]-t[0])/1000))
ds_sorted = system.dataset.toDataSet(headers, data_sorted)
if 'PyDataSet' in str(type(dataset)):
ds_sorted = system.dataset.toPyDataSet(ds_sorted)
if DEBUG: print 'sorted dataset:\r\n'
if DEBUG: system.dataset.print(ds_sorted)
return ds_sorted
