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.
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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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.-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
OK, so this was really lame on my part. But since I bugged everybody
enough to post, i would post everybody my find.
The match does order descending by scores. My problem was due to the
"in boolean mode". I did not understand the behavior of this feature
correctly. What I thought the was: "In boolean mode works just like
regular mode except you can use + and - as "has to" and "cannot
have"
modifies on words." That is not correct.
I got rid of the in boolean mode thing and the search works great
ordering descending by score. Here is a couple of SQL examples I used
to figure this out:
The non-boolean way. 9 rows returned
mysql> SELECT id, MATCH(stext) AGAINST (''bits'') AS score
FROM
search_texts WHERE MATCH(stext) AGAINST(''bits'') ;
+-----+------------------+
| id | score |
+-----+------------------+
| 775 | 4.2670655250549 |
| 776 | 4.2670655250549 |
| 777 | 4.2670655250549 |
| 721 | 3.7272720336914 |
| 773 | 3.5047345161438 |
| 21 | 2.2674028873444 |
| 10 | 0.91718238592148 |
| 405 | 0.65290474891663 |
| 268 | 0.1820078343153 |
+-----+------------------+
9 rows in set (0.00 sec)
The scores are a very good reflection of the relevancy of the term.
records returned in that order.
Second search in boolean mode.
mysql> SELECT id, MATCH(stext) AGAINST (''bits'' in boolean
mode) AS
score FROM search_texts WHERE MATCH(stext) AGAINST(''bits''in
boolean
mode) ;
+-----+-------+
| id | score |
+-----+-------+
| 10 | 1 |
| 21 | 1 |
| 268 | 1 |
| 405 | 1 |
| 721 | 1 |
| 773 | 1 |
| 775 | 1 |
| 776 | 1 |
| 777 | 1 |
+-----+-------+
9 rows in set (0.00 sec)
I get the same nine rows, but they are ordered by id. And the scores
are all 1. Hmm... score of 1 or 0, boolean mode....
So if you want ordered by relevancy, don''t do the boolean trick.
Thanks for you attention and sorry about such a lame post.
Mike
On Dec 21, 8:41 am, mvargo
<mfva...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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.
>
> 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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---