Would u know if there is a way to have a SORT BY FIELD in a “SQL” like parameter used by a DB.Search (query$)? Check code below:
Function GetNEMRelatedCompanies As NotesDocumentCollection
Dim docCo As notesdocument
Dim Search As String
search = " SELECT Type=""Company"" & IsNemRelated=""1"" "
If Not ThisDB.IsFTIndexed Then
Print "Warning: DB is not full text indexed."
End If
Set GetNEMRelatedCompanies = ThisDB.Search(search, Nothing, 0)
End Function
Subject: Is there a way to have a SORT BY FIELD in a “SQL” like parameter used by a DB.Search (query$)?
Make an LotusScript agent which does the actual FTSearch and then reads the results in an multidimensional array, sorts the array by one of several indexes, and shows the results via a text field which is populated using a NotesRichTextItem on a Form. This is meant for the web.
Subject: Is there a way to have a SORT BY FIELD in a “SQL” like parameter used by a DB.Search (query$)?
Hi,
at first the NotesDatabase-Object has two methods for searching documents in a database.Look again in the help file for details as i will only point the main differencies:
A) NotesDatabase.Search(…
the result is NOT sorted and this can not be achieved
does NOT need a Full-Text-Index to work
returns always exact results according to the selection
B) NotesDatabase.FTSearch(…
the results are sorted, but you only have limited choices :-(( (There is no exact way to say “Sort alphabetically by field ‘XXX’”)
it is fast even on huge databases
database has to be full text indexed for it to work
results are dependant on the status of the ft-Index (which cant be ‘immediately’ actual )
Besides this you can sort your results (DocCollection) after your search. It is slow but it works. Example code:
http://www.openntf.org/Projects/codebin/codebin.nsf/CodeSearch/9D3C707BDC2A378E88256C6E00128FC3
Oh and of course if your “SELECT-Statment” is not variable then you should create a view with it and then get your documents from it. This is the fastest a best method.
… and the question if there are convenient and flexible parameters for the select statment like ‘SORT BY’ or ‘GROUP BY’ then the answer is NO. This belongs to the RDBMS world. :-((
Bye
Hynek
Subject: RE: Is there a way to have a SORT BY FIELD in a “SQL” like parameter used by a DB.Search (query$)?
Thanks for your answer.
Would any one know if this will be available on R7?
Subject: RE: Is there a way to have a SORT BY FIELD in a “SQL” like parameter used by a DB.Search (query$)?
R7 - Nope,
this is just pathetic. As usual, Domino doesn’t provide actual needed methods. Switch to DB2 would be my suggestion 
.
To Domino guys…start thinking and make us something usefull.
Subject: RE: Is there a way to have a SORT BY FIELD in a “SQL” like parameter used by a DB.Search (query$)?
The sample in that link works great, but it’s super slow when it runs on large collections. I have a faster method that works. Code below.
Create 2 views (the one to be searched, and a duplicate, both must be flat [no categories]).
Sub SearchViewSorted
Dim Session as New NotesSession
Dim DB as NotesDatabase
Dim AllView as NotesView
Dim SearchView as NotesView
Dim AllEntries as NotesViewEntryCollection
Dim SearchEntries as NotesViewEntryCollection
Dim SearchDC as NotesDocumentCollection
Set DB = Session.CurrentDatabase
Set AllView = DB.GetView ("mySearchView-All") ' we need the sorted view to get all entries sorted in order of view sort
Set SearchView = DB.GetView ("mySearchView") ' we need search view to perform search
Set AllEntries = AllView.AllEntries ' this all entries sorted
Call SearchView.FTSearch ("mySearchCriteria", 0)
Set SearchEntries = SearchView.AllEntries ' this search results sorted accordining to Ftscore or whatever
' this returns the doc collection sorted
Set SearchDC = SortedCollection (AllEntries, SearchEntries)
' now you have a doc collection based on ft search results, but sorted as the original view was sorted
End Sub
Function SortedCollection (allVEC As NotesViewEntryCollection, sourceVEC As NotesViewEntryCollection) As NotesDocumentCollection
' sort the incoming sourceVEC and return as a notesdoc collection
Dim tmpEntry As NotesViewEntry
Dim entryNum As String, entryList() As String, Tmp As Variant
Dim lstCnt As Integer
Dim sCollection As NotesDocumentCollection
' build the return collection starting w/ an empty collection
Set sCollection = sourceVEC.Parent.Parent.FTSearch (|[Form]="THISFORMDOESNOTEXISTANDWILLNOTRETURNANYRESULTS"|, 0)
' go through the entries & produce a list of all entry numbers, we'll then sort that, then get each entry & put into new collection in order
lstCnt = 0
Set tmpEntry = sourceVEC.GetFirstEntry
While Not (tmpEntry Is Nothing)
entryNum = tmpEntry.GetPosition (".") ' it's a flat view, so the entry num will include no separator anyway
Redim Preserve entryList(lstCnt) As String
entryList(lstCnt) = entryNum
lstCnt = lstCnt + 1
Set tmpEntry = sourceVEC.GetNextEntry (tmpEntry)
Wend
' sort the array
SortArray entryList
' add each entry as a document to the collection, which will now be in sorted order
For lstCnt = Lbound (entryList) To Ubound(entryList)
Set tmpEntry = allVEC.GetNthEntry (Cint (entryList(lstCnt))) ' get entry
Call sCollection.AddDocument (tmpEntry.Document) ' add doc
Next
Set SortedCollection = sCollection ' return
End Function
Function SortArray (Array$())
’ this code taken from Notes.net, and slightly modified to sort by numeric value
Dim MaxElement%, MidPoint%, I%, J%, Done%
MaxElement% = Ubound(Array$)
Select Case MaxElement%
Case 0
Exit Function
Case 1
If Cint (Array$(0)) > Cint (Array$(1)) Then
Swap Array$(0), Array$(1)
End If
Case Else
MidPoint% = MaxElement%
While MidPoint% > 0
MidPoint% = MidPoint% \ 2
Do
Done% = True
For J% = 0 To MaxElement% - MidPoint%
I% = J% + MidPoint%
If Cint (Array$(J%)) > Cint (Array$(I%)) Then
Swap Array$(J%), Array$(I%)
Done% = False
End If
Next
Loop Until Done%
Wend
End Select
End Function
Function Swap (A$, B$)
' goes w/ SortArray which was taken from notes.net
Dim Temp$
Temp$ = A$
A$ = B$
B$ = Temp$
End Function