Get sorted results from DQL queries
NotesDominoQuery by now does not support sorting of the results that come out of a query. John Curtis demoed at DNUG46 in Essen how you can get your results sorted. He showed the code only for a second, so I needed to rewrite it from scratch.
The method that he showed leverages from the new ‘maintainOrder’ property that has been added in V10 to the NotesViewEntryCollection class.
But let us first take a closer look at what is needed to make the code work.
I have a small application where I store code snippets. I can categorize them and also test code locally or against a server.
In my sample, I want to get all documents from that application that have “DEMO” in the subject and output the subject in ascending order.
Next I want to get the documents category in descending order.
Both columns, Subject and Category need to be prepared for sorting. They do not neccessarily have to be sorted initially.
To set the sorting programmatically, I use the ‘Resortview’ method from the NotesView class.
Be aware that the columnName must be the programmatic name of the column.
Here is a sample how to use the ‘ResortView‘ method
Dim session As New NotesSession Dim db As NotesDatabase Dim view As NotesView Dim vec As NotesViewEntryCollection Set db = session.currentDataBase Set view = db.getView("Samples") ' Sort By Subject Call view.Resortview("Subject", true) Set vec = view.Allentries ' Sort By Category Call view.Resortview("category", false) Set vec = view.Allentries
vec will now contain all view entries sorted ascending by Subject and after the second ResortView it will contail all view entries by Category in descending order.
You can use the following code to print the result to the console
Private Sub printIt(vec As NotesViewEntryCollection, itemName As String) Dim ve As NotesViewEntry Dim doc As NotesDocument Dim item As NotesItem Dim s As String Set ve = vec.Getfirstentry() While (Not ve Is Nothing) s = "- no value -" Set doc = ve.Document Set item = doc.Getfirstitem(itemName) If (Not item Is Nothing) Then If (item.text <> "") Then s = item.text End if End If MsgBox s Set ve = vec.Getnextentry(ve) Wend End Sub
Now lets add some code to query for all documents that have “DEMO” in the subject.
By now, DQL does not have the capability to build a query that uses CONTAINS. This will be added in a future version of Notes and Domino.
Dim session As New NotesSession Dim db As NotesDatabase Dim view As NotesView Dim vec As NotesViewEntryCollection Dim ve As NotesViewEntry Dim col as NotesDocumentCollection Dim doc As NotesDocument Dim query As String query = "Subject >= 'DEM' AND Subject < 'DEN'" Dim dql As NotesDominoQuery Set db = session.currentDataBase Set view = db.getView("Samples") Set dql = db.CreateDominoQuery() Set col = dql.Execute(query) Call view.Resortview("Subject", true) Set vec = view.Allentries Call vec.Intersect(col, true) Call printIt(vec, "Subject") Call view.Resortview("category", false) Set vec = view.Allentries Call vec.Intersect(col, true) Call printIt(vec, "category")
I put the code into an agent and ran that agent from the console. Here is the output.