Nested Sorting on Multi-dimensional Array

Solar2D Lua Language
#1

I have a multi-dimensional array where I sort on one field at a time with no problem. However, I now need to sort on two or more fields; a sort within a sort or a nested sort if you will. For example, in the snippet below the array is loaded up from a database and it sorts correctly by overall rank (or any single field I choose for that matter). My problem is that I now need to be able to sort first on position then within each position I need to sort on overall rank. I have tried several variations using the table.sort function with no luck. Google has not been too kind on providing information on nested sorting for LUA arrays. Any help is greatly appreciated!   Thanks, Scott

==== array data ====

data[1].name = “Johnson”

data[1].position = “QB”

data[1].overallRank = 5                                

data[2].name = “Smith”

data[2].position = “RB”

data[2].overallRank = 2                                

data[3].name = “Jones”

data[3].position = “RB”

data[3].overallRank = 1                                

data[4].name = “Fenton”

data[4].position = “QB”

data[4].overallRank = 6                                

data[5].name = “Blake”

data[5].position = “QB”

data[5].overallRank = 3                                

data[6].name = “Spencer”

data[6].position = “RB”

data[6].overallRank = 8                                

data[7].name = “Franklin”

data[7].position = “QB”

data[7].overallRank = 4                                

data[8].name = “Davis”

data[8].position = “RB”

data[8].overallRank = 7                                

============

==== desired sorted output ====

position(1)   name     overallRank(2)

QB             Blake                    3

QB             Franklin                4

QB             Johnson               5

QB             Fenton                 6

RB             Jones                   1

RB             Smith                    2

RB             Davis                    7

RB             Spencer                8

========================

===== snippet =====

require "sqlite3" local data = {} -- open database local dbPath = system.pathForFile("rankings.db", system.ResourceDirectory) local db = sqlite3.open( dbPath ) -- read all data from database into array local function readAllData() local i = 0 for rows in db:nrows("SELECT \* FROM rankingsTBL") do i = i + 1 data[i] = {} data[i].name = rows.Name data[i].position = rows.Position data[i].overallRank = rows.OverallRank end end -- Read data from database into array readAllData() -- sort data before placing into tableview for displaying table.sort(data, function(a,b) return a.overallRank \< b.overallRank end) . . (tableview stuff goes here) . .

================

#2

I never did figure out a way to sort a multi-dimensional array/table so instead, I used the ORDER function in sqlite to order the database as it is read which loads the array in the order that I needed. I think this way is probably more “expensive” due to more I/Os being performed but it is the only way I could get it to work. Fortunately, I only have about a thousand records to work with so this method doesn’t cause a blip in performance but if I were dealing with a voluminous amount of records, this would not be a very efficient way to process them for my particular app. I originally wanted to load the array from the database once at the beginning then allow the user to sort the array in several different ways but with this method I now have to “re-read” the database when the user wants to perform multi-level sorting. To accomplish this, I just changed the SQL statement to the following then removed the “table.sort” line altogether. Hope this helps!

for rows in db:nrows([[SELECT \* FROM rankingsTBL ORDER BY position asc, overallRank asc]]) do
#3

if your positions are strictly asc alpha order, and ranks strictly asc numeric order, then, for example:

table.sort(data, function( a,b )

    if (a.position < b.position) then

        – primary sort on position -> a before b

        return true

    elseif (a.position > b.position) then

        – primary sort on position -> b before a

        return false

    else

        – primary sort tied, resolve w secondary sort on rank

        return a.overallRank < b.overallRank

    end

end)

#4

Dave, This works perfectly and I believe will provide improved performance if my database grows. Thanks for the help! 

#5

I never did figure out a way to sort a multi-dimensional array/table so instead, I used the ORDER function in sqlite to order the database as it is read which loads the array in the order that I needed. I think this way is probably more “expensive” due to more I/Os being performed but it is the only way I could get it to work. Fortunately, I only have about a thousand records to work with so this method doesn’t cause a blip in performance but if I were dealing with a voluminous amount of records, this would not be a very efficient way to process them for my particular app. I originally wanted to load the array from the database once at the beginning then allow the user to sort the array in several different ways but with this method I now have to “re-read” the database when the user wants to perform multi-level sorting. To accomplish this, I just changed the SQL statement to the following then removed the “table.sort” line altogether. Hope this helps!

for rows in db:nrows([[SELECT \* FROM rankingsTBL ORDER BY position asc, overallRank asc]]) do
#6

if your positions are strictly asc alpha order, and ranks strictly asc numeric order, then, for example:

table.sort(data, function( a,b )

    if (a.position < b.position) then

        – primary sort on position -> a before b

        return true

    elseif (a.position > b.position) then

        – primary sort on position -> b before a

        return false

    else

        – primary sort tied, resolve w secondary sort on rank

        return a.overallRank < b.overallRank

    end

end)

#7

Dave, This works perfectly and I believe will provide improved performance if my database grows. Thanks for the help!