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.

Happy coding!

One thought on “Get sorted results from DQL queries

Leave a Reply

Your email address will not be published. Required fields are marked *

sixteen + 14 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.