Lucene search/sort question

[Migrated From Jforum.net]

We're extending JForum's search page, and stumbled upon some strange behavior. Basically, sorting the results by date does not actually sort them by date, but some other way that isn't obviously sorted at all. Looking at the source code, I see the following in LuceneSearch.getSorter:

if ("time".equals(args.getOrderBy())) {
	sort = new Sort(SearchFields.Keyword.POST_ID, "DESC".equals(args.getOrderDir()));
}

I suspect that the first parameter should be "SearchFields.Keyword.DATE", not "SearchFields.Keyword.POST_ID". Can someone confirm this, or am I not thinking straight?
[originally posted on jforum.net by udittmer]

[Migrated From Jforum.net]

I'm part of the 'we' extending the software udittmer referred to. I figured out what was going on. Lucene is returning the correct values. (post id are date should be equivalent here)

Then GenericLuceneDAO.getPostsData generates the following SQL:

SELECT p.post_id, p.forum_id, p.topic_id, p.user_id, u.username, u.user_first_name, u.user_last_name, p.enable_bbcode, p.enable_smilies, p.post_time, pt.post_subject, pt.post_text, t.topic_title 
FROM jforum_posts p, jforum_posts_text pt, jforum_users u, jforum_topics t 
WHERE p.post_id IN (112,111,109,108,105,104,103,102,101,100,98,97,96,95,91) 
AND p.post_id = pt.post_id  AND p.topic_id = t.topic_id AND p.user_id = u.user_Id;

Which the database is NOT guaranteed to return in the order the in clause specifies. In our case (postgresql 8.2), the results were returned in a different order. Therefore the results return to the users unsorted.

I fixed it in our local installation by adding a call to the following method at the end of getPostsData().

/**
	 * SearchModel.getPostsDataForLucene returns the posts in the order they are
	 * stored in the database which may not be the same as the order original
	 * requested. This method resorts them in the original order returned by
	 * Lucene.
	 * 
	 * @param postIds
	 * @param l
	 * @return
	 */
	private List<Post> resortByPostId(int[] postIds, List<Post> posts) {
		Map<Integer, Post> postsById = new HashMap<Integer, Post>(postIds.length);
		for (Post post : posts) {
			postsById.put(post.getId(), post);
		}
		
		List<Post> result = new ArrayList<Post>();
		for (int postId : postIds) {
			Post post = postsById.get(postId);
			
			// shouldn't be null, but just in case there is no match
			if ( post != null) {
				result.add(post);
			}
		}
		return result;
	}

[originally posted on jforum.net by boyarsky]

[Migrated From Jforum.net]

Good to know, this bug was new for me.

About your code, just adding an ORDER BY clause to the original sql query wouldn't fix the problem?

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Rafael Steil wrote:About your code, just adding an ORDER BY clause to the original sql query wouldn't fix the problem?

No. The DAO is called regardless of sort order. In JForum, the options are relevance and date. Date/post id could potentially use order by with an API change to tell the DAO what to sort by. However, this isn't useful because relevance can't be sorted at the database end.

I'm reasonably confident the relevance sort has the same problem - that items within a page aren't sorted correctly. It's much harder to prove or disprove that of course! And as I have a fix, it's not really worth it at this point.

-Jeanne

[edited to fix double quoting]
[originally posted on jforum.net by boyarsky]