+- LiveCloud Forums (https://forums.livecloud.io)
+-- Forum: General (https://forums.livecloud.io/forumdisplay.php?fid=1)
+--- Forum: Code Samples (https://forums.livecloud.io/forumdisplay.php?fid=7)
+--- Thread: code to unpack query array to tab-delimited data? (/showthread.php?tid=96)
code to unpack query array to tab-delimited data? - sltfn - 08-19-2019
Hello. I am very new to and still much in the dark about arrays. I am looking for efficient code to unpack the output from a query to lines of tab-delimited data (one record per line). Here is the code I've come up with so far. I'd guess there MUST be a more efficient approach.
on mouseup
## query constructed above
put cdb_batchQuery(tInputA,"cloud","logicalAND","recordData") into tOutputA
put flattenArray( tOutputA) into flattened -- see flattenArray function below
end mouseup
function flattenArray A , d1, d2, T, X
if T is empty then
if d1 is empty then put cr into d1
if d2 is empty then put tab into d2
end if
if A is not an array then return T & X & A & d1
repeat for each key K in A
put flattenArray( A[K], d1, d2, T, X & K & d2 ) into T
end repeat
end flattenArray
This yields the queried records in this format (just one record of many shown here).
[CDB Table ID] 1 [CDB Record ID] ATTRIBUTES fopn
[CDB Table ID] 1 [CDB Record ID] LAST_NAME Jordan
[CDB Table ID] 1 [CDB Record ID] STATUS linnm
[CDB Table ID] 1 [CDB Record ID] RANK 2
[CDB Table ID] 1 [CDB Record ID] CITY San Francisco
[CDB Table ID] 1 [CDB Record ID] YEAR 2019
[CDB Table ID] 1 [CDB Record ID] ROLE 5
[CDB Table ID] 1 [CDB Record ID] NAME Sam Jordan
[CDB Table ID] 1 [CDB Record ID] DATE 2019-02-17
[CDB Table ID] 1 [CDB Record ID] cdb cdbDateModified 1562677087
[CDB Table ID] 1 [CDB Record ID] cdb cdbDateCreated 1562677087
[CDB Table ID] 1 [CDB Record ID] cdb cdbRecordID [CDB Record ID]
[CDB Table ID] 1 [CDB Record ID] cdb cdbTableID [CDB Table ID]
[CDB Table ID] 1 [CDB Record ID] cdb cdbRecordVersion 1
[CDB Table ID] 1 [CDB Record ID] cdb cdbTableName stats
[CDB Table ID] 1 [CDB Record ID] cdb cdbCloudSyncVersion 1
I then loop through the lines and remove the first two columns, then I filter out the lines with "cdb" keys not needed in my app as long as I preserve the record ID, and get a list of records like this…
[CDB Record ID] ATTRIBUTES fopn
[CDB Record ID] LAST_NAME Jordan
[CDB Record ID] STATUS linnm
[CDB Record ID] RANK 2
[CDB Record ID] CITY San Francisco
[CDB Record ID] YEAR 2019
[CDB Record ID] ROLE 5
[CDB Record ID] NAME Sam Jordan
[CDB Record ID] DATE 2019-02-17
Finally, I loop through the lines again and put the data for each record into the tab-delimited format I need, which is something roughly like this…
fopn Jordan linnm 2 San Francisco 2019 Sam Jordan 2019-02-17 [CDB Record ID]
ucpn Smith linnm 1 Los Angeles 2019 Susan Smith 2019-06-23 [CDB Record ID]
efrain.c and mark_talluto's suggestions for batch record creation and updates were VERY helpful. Thank you.
Does anybody have a simpler solution for this problem? Pretty sure there must be one (many). Thanks in advance.
RE: code to unpack query array to tab-delimited data? - efrain.c - 08-21-2019
Hi sltfn,
Here is one way to format the results of a batchQuery into a line delimited list of tab delimited records. I tried to make it as generic as possible so other users can use it if they'd like.
Code:
function flattenArray pBatchQueryA, pKeys, pDelimiter, pIncludeRecordID
local tResults, tRecord
repeat for each key xTableID in pBatchQueryA
repeat for each key xIndex in pBatchQueryA[xTableID]
repeat for each key xRecordID in pBatchQueryA[xTableID][xIndex]
repeat for each item xKey in pKeys
put pBatchQueryA[xTableID][xIndex][xRecordID][xKey] & pDelimiter after tRecord
end repeat
if pIncludeRecordID then put xRecordID & pDelimiter after tRecord
delete char -1 of tRecord --remove trailing delimiter
put tRecord & lf after tResults
put empty into tRecord
end repeat
end repeat
end repeat
delete char -1 of tResults --remove trailing lf
return tResults
end flattenArrayA couple things to note:
- put flattenArray(tOutputA) into flattened will have to be changed to put flattenArray(tOutputA, "attributes,last_name,status,rank,city,year,name,date", "tab", true) into flattened.
- the code assumes that the batchQuery was only performed on one table. If this is not the case, you can pass another parameter after pBatchQueryA (pTableID) that will specify the table you are interested in. The outer repeat that uses xTableID will need to be removed and all references of xTableID will need to be changed to pTableID.
Hope this helps! Let me know if you have any questions.
RE: code to unpack query array to tab-delimited data? - sltfn - 08-21-2019
Thanks, efrain! This cut processing time from my inefficient example above by 60%. In the interim I had devised a method that cut processing time by 30+% but this is much better and also taught me some things.
RE: code to unpack query array to tab-delimited data? - efrain.c - 08-22-2019
Awesome! I'm glad to hear you're getting a hang of arrays. That's a great improvement. If you don't mind sharing, I'm interested to see how my suggestion compares in processing time.
RE: code to unpack query array to tab-delimited data? - sltfn - 08-22-2019
Oops. Hadn't seen your reply above so edited my reply. Your suggestion cut processing time by 60% from my stumbling initial approach; processes in 40% of the time. Thank you.