Wednesday, January 20, 2010

XPages: Three ways to build a search interface with "on the fly" sortable columns

While copy/paste programmers and XPages novices might get something out of this demoapp/blogpost, it's directed towards the people that want to push XPages beyond the constraints of the drag and drop interface in Domino Designer.

I detest the "need" to have a Notes view for every kind view in XPages. Recently, at work, I've been occupied mostly by creating customizable search/filtering views in XPages.

Until today, I've used a DataTable with a SSJS function as data source (returns JS-objects). The function does an FTSearch on a NotesView, and fetch precalculated JSON strings from a view column. The DataTable can be designed to use one/all fields from the json-objects. One DataTable column can have one or more values from the JSON object (e.g. dataItem.fieldName + ' ' + dataItem.fieldName2). This has the advantage of only needing a one (unsorted) column view to generate a wide array of reports.

Julian Buss seems to have been working on/pondering solutions for "on the fly" sortable searches as well. I've implemented what I think he meant in the demoapp as well. I didn't take the time to write caching algorithms for his way, using NotesViewEntryCollection. I also didn't take the time to implement descending sort. The reason I was so lazy was that I didn't like my implementation of his idea.

The last way (default choice in demoapp), and the best way I've found so far is to do FTSearch on the db. In the demoapp, I search the entire db without restricting to a form/etc. This is because I didn't find the need for restricting the search as there are only one kind of documents in the demoapp. The result from the search is a NotesDocumentCollection. The datasource is a function that accepts what I call an array of field definitions.

E.g.
var fieldDefinitons = [{
fieldName: 'someFieldName',
dataType: 'string'
},{
fieldName: 'someFormulaString',
dataType: 'date'
}]


Supported datatypes in the demoapp are string, multi-value string, date, multi-value date, number and multi-value numbers. The field definitions are parsed into a string that can be evaluated against a document (using session.evaluate). Using session.evaluate is the fastest way I know of to get multiple values out of a document. Compared to fetching precalculated JSON strings from a view, there is no discernible difference in speed when evaluating against documents in the NotesDocumentCollection.

Regarding caching, the JSON object is stored in a viewScope-variable. When the user navigates to another page, the JSON object should be recycled from what I understand of XPages/JSF. The values are only fetched from the server when the query string is changed. When the used wants to sort the view, the cached object is resorted. This results in speedy pagination/sort.

On my local computer, with 5000 items in the result-object, re-sort is done in about 100ms on the server. It probably takes 500-1000ms before the user gets what he ordered. The initial search takes 2-4 seconds (server time).

I could probably write a tiny book on the "technology"/thought/history behind this demoapp. Instead, I'll tease you with an animated gif, and let you download a demoapp (here be messy code). The demoapp is a biggie. It's a mutilated version of Jake's FakeNames application. I chose this because I wanted to test against a "big" database. This has 40k NAB Person documents.

Screenshots (animated gif):


>> Demoapp (6 MB zip)

Share and enjoy!

41 comments:

Baiju said...

Tommy,

Nice demo and concept. Thanks for sharing..

Baiju

Unknown said...

Excellent post Tommy, thanks for sharing that with us.

Unknown said...

First, thanks so much for posting this!

I'm trying to incorporating your code into a project I'm working on but have run into a couple of problems, both regarding the use of dates. The first issue is that if I specify a value as a 'date' type and the underlying field (or formula used) does not return a date but an empty string then the process errors out. The other issue is that I need to to be able to specify a date-time value (not just a date) in the format MM/dd/yyyy HH:mm. I'm struggling though on how to modify your script library to handle those so if you have suggestions I would appreciate it. Thanks.

-Ernie

Tommy Valand said...

Date-bug fixed and Date/Time implemented in the NotesDatabase.FTSearch/NotesDocumentCollection search engine, which is the one I recommend using.

Please download the demoapp again for updated code.

Unknown said...

I downloaded a new copy of the demoapp and incorporated the revised code into my app. Works peachy-keen now! Thank you for the very quick and thorough response to my request!

Tommy Valand said...

No problem.. :)

Julian Buss said...

During more tests today I realized that my approach has a lot potential for optimization when combined with an idea of yours :-)

I cannot use database.ftsearch because I have often the situation that I simply want to replace a regular NotesView.ftsearch() call from somewhere, without rewriting lots of other code.

Therefore I will stick with the NotesView.ftsearch()... the problem with my current implementation of putting the resulting ViewEntries of a ftsearch in an HashMap is that I have no caching yet.

And I cannot simply but the HashMap into the viewScope, since the underlying NotesViewEntry objects are recyled after the page is displayed.

I'm playing around with putting the columnValues of each ViewEntry into an object of my own, then put that into the HashMap, and THAT HashMap could be cached.

I'm getting back when I have results :-)

Tommy Valand said...

Great!

I look forward to hearing what you come up with.. Hopefully I can get inspired from your solution, and further improve my (current) favourite implementation of sortable search. :)

Julian Buss said...

http://www.juliusbuss.de/web/youatnotes/blog-jb.nsf/dx/follow-up-to-sorted-ftsearch-results-in-xpages-with-code.htm

Unknown said...

Tommy,

I've made some tweaks to your sorting library, mostly parameterizing some of the values to make it more generic, but there is one feature I implemented which you might want to consider in your own code, which is the ability to specify a secondary sort field, so that the results sort first by the primary field then by the secondary field (if specified).

Basically, in the genericSort function you can add the below code to refine the sorting based on a secondary field. You would of course need to add fieldName2 as a parameter to the all the appropriate functions and store that field name in another viewScope variable just as you do with the primary sorting field.


//secondary sort field
if(fieldName2!==""){ //
var fieldAa = a[fieldName2];
var fieldBb = b[fieldName2];
if(( fieldA === fieldB )&&( fieldAa > fieldBb )){ return 1 * multiplier; }
if(( fieldA === fieldB )&&( fieldAa < fieldBb )){ return -1 * multiplier; }
}

Tommy Valand said...

Thanks.. While I don't have the need for it right now, it could come in handy in the future.

Regarding generalizing the code. That's what everybody who downloads my demoapps should do..

Few/none of the demoapps I post are ready for production. I try to iron out bugs, but I don't spend much time cleaning up the code.

Anonymous said...

Hi Tommy,

Thanks for sharing your skills with us, I have one question about xpages, i am searching in FT database and i want to get how many results the search returned. You could put count field on a form and it returned the result count but how do you do that in xpages.

Tommy Valand said...

When you say you FT search in a db. Do you mean a view control, or using one of the methods I specified, or something entirely different?

Anonymous said...

@Tommy
thanks sharing your code with us.
However I cannot run it on a Domino Server 8.5. On a Machine with Domino Server Version 8.5.1 it is working fine.
Do you know any reason for that?
Thanks
Berk

Tommy Valand said...

The syntax (in the source code) for event handlers changed from 8.5.0 to 8.5.1. See comment from Tim Tripcony here for description/workaround.

Sumit Tayal said...

Tommy,

Great work and thanks for sharing. I have a small query, Is it possible to have category column in data table. If yes, please help me on that.

Thanks heaps.

cheers, Sumit

Tommy Valand said...

I don't think so, looking at All properties on a DataTable column. I also inspected a categorized column in a view control using the XPages API Inspector. It's identical to a flat column. It looks like it's the underlying data model (view) that controls categorized vs non-categorized.

I think the easiest way to emulate a categorized column is to use a nested repeat control.

The topmost repeat control has a section, while the "nested" repeat control has the "subcategories".

If you have multiple categories, create more levels of repeat controls.

Anonymous said...

is there any know server side @sort function?

Tommy Valand said...

Not sure what you're looking for, but if you're looking to sort arrays, use array.sort.

Sudatta said...

Hi Tommy,

The search you have provided is really great.

Have you ever tried implementing a search feature in a dojo picklist?

How to implement a generic search that will fetch results if the search criteria is a combination of text and number for example : "IT2010".This doesn't work in notes or x-pages .

Can you please provide any help or suggestions.

Thanks a lot in advance.

Sudatta

Anonymous said...

Hi Tommy,

The search you have provided is really great.

Have you ever tried implementing a search feature in a dojo picklist?

How to implement a generic search that will fetch results if the search criteria is a combination of text and number for example : "IT2010".This doesn't work in notes or x-pages .

Can you please provide any help or suggestions.

Thanks a lot in advance.

Sudatta

Tommy Valand said...

I must admit I'm not entirely sure what you're looking for.. :)

By Dojo Picklist, do you mean type-ahead, like this or this.

Sudatta said...

Hey Tommy,

Sorry to bug you again.Just a small help needed if you can.

I'm trying to set the values for the multivalue field.

For the first time it works fine but later doc.getItemValue returns all the values as a single string and again it appends the new value in it as a next value.
It happens like this :

First time : Value1;
Value2
Second time: Value1 Value2;
Value3

Could you please suggest something on this?

Thanks in advance for the help.

Tommy Valand said...

Since this is something that others might struggle with, I wrote a small blogpost with a code snippet regarding your issue.

Ravi said...

This is great and thanks for sharing the code. A nice enhancement would be to provide a filter and then do a field search so that it will work as column filter and then the results can be sorted as well.

Ed said...

Thanks for your tips Tommy, I seem to find myself at your website again and again for inspiration.

I have a question. With this approach could you display images depending on whats in the rows of data? when I try to reference the 'collection name' the same way I would a normal view or repeat I cant seem to get it to work ie
if(dataItem.officeCountry="Lebanon"){
"imagesname"}

Also could you use these methods just with a repeat to get the sorting instead of a search result?

Sorry if these are dumb questions, im a late arrival to the xpages party...

Tommy Valand said...

Thanks for the kind words :)

There are several ways you could put an image inside the table. You could use a static object with the country as key, and the url to the image as value.

E.g.
// Get the path to the db
var dbPath = '/' + database.getFilePath().replace( /\\/g, '/' ) + '/';
var countryImages = {
lebanon: dbPath + 'lebanon.gif',
norway: dbPath + 'norway.gif'
}

To get the url,
countryImages[ dataItem.officeCountry ] This returns '/path/db.nsf/lebanon.gif' if officeCountry equals 'lebanon', and the path to the db is '/path/db.nsf'.

I haven't used the image control a lot, so I can't remember how they work, but if you set the computed fields to display html, you could do this to get an image:
'<img src="' + countryImages[ dataItem.officeCountry ] + '" />'

Regarding the second question, take a look at this blogpost. You can convert the JSON from the view to an array of JSON objects using something like this.

Then you can sort the objects using the native sort method.

Tommy Valand said...

And.. If you need a little introduction to JavaScript, Keith Strickland just posted a few links that should provide a good introduction to the language.

Anonymous said...

Thanks for sharing this really interesting code.

I had a problem when sorting dates. To fix this problem, I had to modify the function genericSort included in the sortJSONArray function like this :

function genericSort( a, b ){
var fieldA = a[fieldName];
var fieldB = b[fieldName];

if(a[fieldName].getClass().getName()==="java.util.Date") {
if (fieldA.getTime()>fieldB.getTime())
return 1 * multiplier;

if (fieldA.getTime() < fieldB.getTime())
return -1 * multiplier;
} else {
if( fieldA > fieldB ){ return 1 * multiplier; }
if( fieldA < fieldB ){ return -1 * multiplier; }
}
return 0;
}

Tommy Valand said...

Strange.. I use more or less the same code I made for the demoapp to sort object arrays, and I haven't had any problems with sorting by java.util.Date properties using < and >.

Anonymous said...

Hello Tommy and happy new year.

OK, forget my post concerning the date problem. I simply had a field returning a null value (not detected because I had to sort a lot of documents and only one was concerned by this problem). I corrected this value in the JSON definitions object and now, everything works fine.

Thank you very much for your code.

Anonymous said...

any reasons why you do not use JSONQuery to sort / filter content?

Tommy Valand said...

No reason :)

Bryan S said...

Tommy,

This is a great post. I am modifying your code for use in a production db.

I noticed a bug in your code, I *think*. I type in Pete and use the NotesViewEntries. I get 29 results, including two for the country of Slovenia. If I sort on Country, I only get one entry for Slovenia. I noticed this in my code too. This doesn't seem to happen in the other two methods.

Tommy Valand said...

You're correct. When you sorted a column with similar values (e.g. Slovenia), only one of the entries remained in the result.

This is due to the sorting is being done by a TreeMap, which can only have unique keys.

I fixed this by adding the NoteId of the document to the sorting key. It seems to work properly now.

I've updated the demoapp.

Thanks for the feedback :)

Rashid Azar said...

I implemented this view sorting without using FT search. You can have a look at my blog http://blog.ecafechat.com/2012/01/20/xpage-sort-view-column/

Anonymous said...

How do I set correct datatype for viewentry method? just as in Template method. I want to check the datatype for the view entry but not sure as it doesn't return an item for item.type??

Anonymous said...

Thanks for this post.

I try your code and for me is ok.

The only question is about performance impact. If i decide of replace the view control anyway in my application can be problem.?

Alessandro

Tommy Valand said...

The standard View Control will have better performance.

I don't recommend using this technique in a search interface where you can have thousands of search result items.

dr. Theory said...

I have the wierdest problem...

In SearchUtilitiesSSJS\\Lookup this.getColumnValuesFromEntryCol returnes an empty array but the array isn't really empty, because length (count) is 3100..
And I log values being pushed in getColumnValuesFromEntryCol function, and values are there.. but return is empty.. ??? don't get it.. even tried to use java.util.Vector

dr. Theory said...

Ha! :) in your example the column in JSON view is set as DateTime which always returns an object.. but when I did my own view it returned string for each row.. so I just had to parse the string to JSON object...