Sort multidimensional array

[ittarter]

So I have an array tScores created from an SQL query. It's a multidimensional array where each record was added with a counter, and then each field is labeled and given a value.

e.g. of a single simplified record:
tScores[1]["Date"] = 12/25/16
tScores[1]["ID"] = 0105
tScores[1]["Email"] = user1@example.com

From there, I'm trying to SORT this array using the guide for a one-dimensional array:
http://lessons.livecode.com/m/4071/l/11 ... t-an-array

But it doesn't work:

Code: Select all

get the keys of tScores
   sort lines of it international ascending by tScores["ID"]
   split it by return
   put 1 into tNextIndex
   repeat for each element tIndex in it
      put tScores[tIndex] into tSortedArray[tNextIndex]
      add 1 to tNextIndex
   end repeat

--

Code: Select all

sort lines of it dateline descending by tScores["Date"]

similarly does not work.

I have a theory that it doesn't work because it's looking in the wrong 'dimension' of the array for the key "ID". Is this correct? If so, how do I correct my code so that the sort function looks in the second dimension rather than the first?

OPTION 2. Since this array is going into a datagrid, I would also be happy to just sort the dgData of the datagrid directly. However the guide on this page
http://lessons.livecode.com/m/datagrid/ ... y-s-values
results in a semantic error (missing an end paren, which is odd because there are no parens in that code or in mine). I've never used the "dispatch" command and I don't really know how to adapt what I'm seeing in the guide to a simple mouseup on the group itself. Do I need to WRITE the sortDataByKey command somwhere, or is it automatically understood by the datagrid itself?

[MaxV]

You are using sort with some errors, the correct code is:

########CODE to copy and paste#######
get the keys of tScores #you should get just 1,2,3 and so on... one per line
sort lines of it datetime by tScores[each]["Date"]
put it into newOrder
#how we have a list of numbers 3,1,2 that is just the list of the items
put 0 int tCount
repeat for each line tLine in newOrder
#... create the new array
put tScores[tLine]["Date"] into tScoresSorted[tCount]["Date"]
put tScores[tLine]["Email"] into tScoresSorted[tCount]["Email"]
put tScores[tLine]["ID"] into tScoresSorted[tCount]["ID"]
#... and so on...
add 1 to tCount
end repeat
#####END OF CODE generated by http://tinyurl.com/j8xf3xq #####

[ittarter]

Thanks for your help, MaxV! Everything works fine now.

[jameshale]

I noticed you referred to the Data Grid lessons on the livecode site.
If you are using version 8 or 9 of livecode the updated Data Grid guide can be found under the "Guides" tab of the Dictionary window. All but one example have been updated and tested to work.

[ittarter]

Thanks, James! So that means that the site guides have NOT been updated?

[jameshale]

Unfortunately not (certainly for the Data Grid and the few others I have looked through.)

[Simon Knight]

Hi,

For anyone finding this post and wanting to sort an array it is worth looking at this lesson : https://lessons.livecode.com/m/4071/l/1 ... t-an-array

The lesson uses a similar approach to above but converts the results back into an intermediate array. Also there is no need to copy each element across to the new sorted array. My version of array sort is here :

Code: Select all

Function SortDataByCreationMinutes @pRawFileDataA
   get the keys of pRawFileDataA #you should get just 1,2,3 and so on... one per line
   sort lines of it by pRawFileDataA[each]["CreationSeconds"]
   put it into newOrder
   split it by return  -- creates an array
  
   put 1 into tNextIndex
   repeat for each element tKey in it
      #... create the new array
      put pRawFileDataA[tKey] into tSortedFileDataA[tNextIndex]
      add 1 to tNextIndex
   end repeat
   
   return tSortedFileDataA
   
end SortDataByCreationMinutes

For reference pRawFileDataA looks like this

Screenshot of array structure

2021-02-27-145431-Screenshot 2021-02-27 at 14.54.25.png (27.25 KiB) Viewed 10221 times

Skids

[FourthWorld]

Unfortunately not (certainly for the Data Grid and the few others I have looked through.)

That's a problem. Do you know if a documentation bug report has been filed on that?

[stam]

My version of array sort is here...

Thanks for posting that Skids

Looking to generalise your handler i've made it a bit more generic by adding the sort key as a parameter.
I did also want to pass sortType and sortDirection as parameters to make it fully reusable, but then it generates an error when doing so:

Code: Select all

function sortArray pKey, @pArray, pDirection, pSortType
   local tNextIndex, tSortedArray
   get the keys of pArray 
   sort lines of it pDirection pSortType by pArray[each][pKey] //fails here, asks for a comma but only if pDirection and pSortType are included
   split it by return  
   repeat for each element tKey in it
      add 1 to tNextIndex
      put pArray[tKey] into tSortedArray[tNextIndex]
   end repeat
   return tSortedArray
end sortArray

I presume in the above pDirection and pSortType are interpreted as additional containers to sort, rather than qualifiers for the sort, since the error states there is a missing ','
The syntax in the documentation is: sort [{lines | items} of] <container> [<direction>] [<sortType>] [by <sortKey> ]
Is there any way around this?
I'd loath to have multiple versions to cater for numeric/date etc and ascending/descending, although i suppose i could add nested conditional statements inline to keep it as 1 function... (ugh!)

[stam]

i could add nested conditional statements inline to keep it as 1 function... (ugh!)

This now works and is fully generalisable for array sorting - specifying a key, sort direction and sort type as parameters... but the code is now is so much longer and unpleasant.
Grateful on suggestions to refactor this and get rid of the nested conditional statements - simply specifying sortDirection and sortType directly generates an error for me as above...

Code: Select all

function sortArray pKey, @pArray, pDirection, pSortType
   local tNextIndex, tSortedArray
   get the keys of pArray 
   if  "desc" is in pDirection then 
      switch pSortType
         case "international"
            sort lines of it descending international by pArray[each][pKey]
            break
         case "numeric"
            sort lines of it descending numeric by pArray[each][pKey]
            break
         case "datetime"
            sort lines of it descending datetime by pArray[each][pKey]
            break
         case "text"
            sort lines of it descending text by pArray[each][pKey]
            break
         case "binary"
            sort lines of it descending binary by pArray[each][pKey]
            break
         default
            sort lines of it descending text by pArray[each][pKey]
      end switch
   else
      switch pSortType
         case "international"
            sort lines of it ascending international by pArray[each][pKey]
            break
         case "numeric"
            sort lines of it ascending numeric by pArray[each][pKey]
            break
         case "datetime"
            sort lines of it ascending datetime by pArray[each][pKey]
            break
         case "text"
            sort lines of it ascending text by pArray[each][pKey]
            break
         case "binary"
            sort lines of it ascending binary by pArray[each][pKey]
            break
         default
            sort lines of it ascending text by pArray[each][pKey]
      end switch
   end if
   
   split it by return  
   repeat for each element tKey in it
      add 1 to tNextIndex
      put pArray[tKey] into tSortedArray[tNextIndex]
   end repeat
   return tSortedArray
end sortArray

[bobcole]

Interesting topic. I have always had trouble working with arrays; they are somewhat mysterious to me but I know they are very useful. I haven't tried sorting an array until now.
I played with it until I figured out how it works with a simple array. Previous contributions here helped me significantly; thanks.
Below is a stack that shows what I did. Please feel free to modify it for your own purposes.
Hope it helps,
Bob

Array Sort Example.livecode.zip

(1.46 KiB) Downloaded 198 times

[Thierry]

This now works and is fully generalisable for array sorting - specifying a key, sort direction and sort type as parameters... but the code is now is so much longer and unpleasant.
Grateful on suggestions to refactor this and get rid of the nested conditional statements

Hi Stam,

As you ask, here is one way!
But be careful as I did a test with only a 1 dimensional array, but it should work with any.
The idea is to drop completely the 2 switch statements, and use instead
a do merge() to dynamically edit the sort command based on your parameters...

Code: Select all

function sortArray_tdz pKey, @pArray, pDirection, pSortType
   local tNextIndex, tSortedArray
   get the keys of pArray
   do merge("sort lines of IT [[pDirection]] [[pSortType]] by pArray[each]")
   repeat for each line tKey in IT
      add 1 to tNextIndex
      put pArray[tKey] into tSortedArray[tNextIndex]
   end repeat
   return tSortedArray
end sortArray_tdz

and how I test it:

Code: Select all

on test_sorting_array
   local X = "ccc,eee,ddd,xxx,bbb,ppp,aaa"
   split X by comma
   put X[1] && X[2] &cr into fld 1 --> ccc eee

   put sortArray_tdz( empty, X, "ascending", "text") into X
   put X[1] && X[2] &cr after fld 1 --> aaa bbb

   put sortArray_tdz( empty, X, "descending", "text") into X
   put X[1] && X[2] &cr after fld 1 --> xxx ppp

end test_sorting_array

Enjoy new day, new month.

[stam]

use instead a do merge() to dynamically edit the sort command based on your parameters...

Ah, thank you Thierry! Merge() was the function I was missing, looks like the ticket. Will test after work but looks like it should work perfectly, thank you!

Happy new month to you too

[stam]

Thanks once again Thierry and everyone who contributed to this discussion.
@Thierry - great code- was unaware of the merge() function, works a charm but you were missing the [pKey] after pArray[each] in the merge() statement. Works like a charm once added

@bobcole, @skids: good code, i was doing the same having been inspired by code on this forum, but was looking for a way not to have to hard-code sort type and sort direction as that would generate a number of same-ish handlers every time i wanted to sort by different direction, or sort numerically or by date etc. More code to maintain, harder to debug...

Having just one function to do all the above means i just keep 1 function in the stack script and can sort any multidimensional array with any criterion...I've changed the parameter order to match the Sort function.

I've added just a bit of error checking in case someone using this (mainly me!) put the wrong parameter in the wrong position.
Accepted sortDirection params: Ascending, Descending, or empty (-> defaults Ascending)
Accepted sortType params: International, Numeric, DateTime, Text, Binary, or empty (-> defaults to Text)
If verification fails, the dev is notified and the original array is returned - hopefully will help debugging some...

Hopefully this is the definitive code to sort out all array sorting (ehm... excuse the pun )

Code: Select all

function sortArray @pArray, pDirection, pSortType, pKey
   local tNextIndex, tSortedArray
   if (pDirection is not in "ascending,descending" and pDirection is not empty) or \
        ( pSortType is not in "international,numeric,datetime,text,binary" and pSortType is not empty) then
      answer "Malformed parameters - sortDirection must be either Ascending, Descending, or empty". & return & \
            "SortType must be either international, numeric, datetime, text, or binary, or empty."
      return pArray
   end if
   get the keys of pArray
   do merge("sort lines of IT [[pDirection]] [[pSortType]] by pArray[each][pKey]")
   repeat for each line tKey in IT
      add 1 to tNextIndex
      put pArray[tKey] into tSortedArray[tNextIndex]
   end repeat
   return tSortedArray
end sortArray

Do post a reply if you spot a problem with this code!

[Thierry]

@Thierry - great code- was unaware of the merge() function, works a charm
but you were missing the [pKey] after pArray[each] in the merge() statement. Works like a charm once added

Mmm sure but actually I told you about this:

But be careful as I did a test with only a 1 dimensional array...

Glad that it works for you

Kind regards,

Thierry