Did you know that cfsearch allows you to search against multiple collections? As long as you aren't searching against categories you can search against as many collections you want. You simply add them to the COLLECTION attribute and go for it. However, a reader noticed something odd. His results were sorted by collection, not be score. So all the results for the first collection were returned in the results first followed by results in the second query. To test this myself I created two collections. The first contained all the HTML files from the ColdFusion docs. I called this collection cfref. I then created another collection of the Word docs from CFWACK. These are my own copies and are just a small part of the book but I thought it would give me nicely similar content to search against. I then tried the following code:
<cfsearch collection="cfref,cfwack" criteria="cflog" name="results" maxrows=20>
<cfdump var="#results#" show="score,title">
Which game me...
As you can see, the Score resets back up for the second collection. Not good. So I suggested a simple query of query. That should work, right?
<cfquery name="newresults" dbtype="query">
select title, score
from results
order by score desc
</cfquery>
Nope. Didn't work at all. On a whim I looked at the metadata for the query and saw this:
See the issue? The score is being returned as varchar. Heck, all the columns are, even recordssearched and size. That's definitely a bug. (I'll file a report for that in a minute.) Luckily the fix is easy enough - just cast your result.
<cfquery name="newresults" dbtype="query">
select title, cast(score as decimal) as realscore
from results
order by realscore desc
</cfquery> <cfdump var="#newresults#">
Which gives us...
P.S. So this is interesting. Notice my use of maxrows? What happens if I use maxrows=5 against the data I mentioned above? I get 6 rows. Apparently the max applies per collection. On one had this is good - you are guaranteed to get the best results back. On the other hand if you just want N rows total, you are kind of screwed. Once again though a query of query will help.