2006-04-06

Notes: Bubble Sorting a Document Collection

«Searching a database using db.ftsearch is pretty fast and efficient but ordering based on a field value in the documents is not possible. What is required is something akin to a SQL ORDER BY clause but alas we don't have one and have to resort to sorting the collection the old fashioned way... by implementing a bubble sort.

Usage

Dim vCollection As NotesDocumentCollection Set vCollection = vSearchDatabase.ftsearch(aQuery, 501, FT_SCORES, FT_FUZZY) Set vCollection = Global_SortCollection(vCollection, "Person_Surname")

The sort function requires two parameters, the first a NotesDocumentCollection with documents in it and the second a field name to use to sort the documents, in the example above the documentcollection "vCollection" will be sorted by "Person_Surname".

The function works by converting the document collection into an array of documents, doing a bubble sort on the array and then converting the array back into a new document collection.


The Code

Function Global_SortCollection(aCollection As NotesDocumentCollection, aField As String) As Variant Dim vCollectionDB As NotesDatabase Dim vDocTemp As NotesDocument Dim vCollectionSorted As NotesDocumentCollection Dim vDocArray() As String Dim vLower As Integer Dim vUpper As Integer Dim vBottomMax As Integer Dim vLoop As Integer Dim vLoopTopHalf As Integer Dim vLoopBottomHalf As Integer Dim vMidPoint As Integer Dim vTarget As String Set vCollectionDB = aCollection.Parent Redim vDocArray(aCollection.count-1) As String 'CONVERT DOCUMENT COLLECTION TO ARRAY Let vLoop = 0 Set vDocTemp = aCollection.GetFirstDocument Do While Not vDocTemp Is Nothing vDocArray(vLoop) = vDocTemp.GetItemValue(aField)(0) + "~" + vDocTemp.UniversalID Set vDocTemp = aCollection.GetNextDocument(vDocTemp) vLoop = vLoop + 1 Loop 'SHELL SORT THE ARRAY vLower = Lbound( vDocArray( ) ) vUpper = Ubound( vDocArray( ) ) vMidPoint = 1 'DETERMINE A STARTING MID POINT TO THE ARRRAY Do vMidPoint = (3*vMidPoint) + 1 Loop Until vMidPoint > vUpper - vLower + 1 'LOOP THROUGH THE ARRAY Do vMidPoint = vMidPoint \ 3 vBottomMax = vLower + vMidPoint - 1 For vLoopTopHalf = vBottomMax + 1 To vUpper vTarget = vDocArray(vLoopTopHalf) vLoopBottomHalf = vLoopTopHalf 'COMPARE TOP HALF OF ARRAY WITH BOTTOM HALF Do While vDocArray( vLoopBottomHalf - vMidPoint ) > vTarget vDocArray(vLoopBottomHalf) = vDocArray(vLoopBottomHalf - vMidPoint) vLoopBottomHalf = vLoopBottomHalf - vMidPoint If (vLoopBottomHalf <= vBottomMax) Then Exit Do Loop If (vLoopBottomHalf <> vLoopTopHalf) Then vDocArray(vLoopBottomHalf) = vTarget Next Loop Until vMidPoint = 1 'CREATE A NEW EMPTY DOC COLLECTION Set vCollectionSorted = vCollectionDB.Search("",Nothing,0) 'CONVERT ARRAY TO DOC COLLECTION For vLoop = 0 To Ubound(vDocArray) Set vDocTemp = vCollectionDB.GetDocumentByUNID(Strrightback(vDocArray(vLoop), "~")) Call vCollectionSorted.AddDocument(vDocTemp) Next vLoop 'RETURN THE DOCUMENT COLLECTION Set Global_SortCollection = vCollectionSorted End Function


Conclusion
A simple yet extremely useful function that belongs in everyone's global routines script library.»




(Source)

No comments:

Post a Comment