mvargo wrote:
The mysql match function returns scores. I am using the mysql full
text indexing (don’t send the ferret sales pitch, I’ve seen it, its
nice, I have my reasons*) but the results don’t seem to be ranked in
order of score. Is there anyway via the rails interface to get at the
scores?
my code does this:
@searchtexts = SearchText.find(:all, :conditions =>
[‘match(stext) against (? in boolean mode)’,
params[:searchtext].to_s], :limit => 20)
ideally i’d like to pass in an :order => “scores DESC” or something
like that.
The mysql docs have this to say about full-text search and ordering by
relevance, see
http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html
The next example shows how to retrieve the relevance values explicitly.
Returned rows are not ordered because the SELECT statement includes
neither WHERE nor ORDER BY clauses:
mysql> SELECT id, MATCH (title,body) AGAINST (‘Tutorial’)
→ FROM articles;
±—±----------------------------------------+
| id | MATCH (title,body) AGAINST (‘Tutorial’) |
±—±----------------------------------------+
| 1 | 0.65545833110809 |
| 2 | 0 |
| 3 | 0.66266459226608 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
±—±----------------------------------------+
6 rows in set (0.00 sec)
So you would probably want this one
mysql> SELECT id, MATCH (title,body) AGAINST (‘Tutorial’) as score
→ FROM articles where MATCH (title,body) AGAINST (‘Tutorial’) order
by score desc
http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html has comments
about adding scores in boolean mode.
You could try adding :select and :order entries to your arguments,
@searchtexts = SearchText.find(:all,
:select => “search_texts.*, match(stext) against
(#{params[:searchtext]}) in boolean mode) as score”,
:order => ‘score desc’,
:conditions => [‘match(stext) against (? in boolean mode)’,
params[:searchtext].to_s], :limit => 20)
Then you will hopefully find the array entries of @searchtexts have a
field called ‘score’.
Good luck,
Stephan
Anybody ever figure this out?
Mike Vargo
*reasons: If I have to scale to more than one mongrel server and I
use ferret I have the problem of multiple servers looking at the same
file system since the indexes are stored on the file system. Just
like you put sessions into the db, you would have to figure this out.
nfs mounts, file locking, rsync with a write master, it goes on. I
don’t deny it can’t be done, but for my application, match() is much
easier.