How to use Doctrine_RawSql for a fulltext search and sorting by relevance

Question

I'm trying to get fulltext searches to be sorted by relevance in a Doctrine_RawSql query.

This code will perform the search:

$q = new Doctrine_RawSql();

$q->select('{p.*}')
  ->from('cms_page p')
  ->where('match(p.content) against (?)', $user_query)
  ->addComponent('p', 'CmsPage p');

This will execute. I would like the results to be sorted by relevance

The real sql would have to look something like:

select 
  p.id, 
  match(p.content) against (?) as score 
from 
  cms_page as p
order by 
  score desc;

So I need to get that match ... against clause in the select... I think.

My crapshoot guess at accomplishing this was:

$q->select("{p.id}, match({p.content}) against ('$escaped_user_query') as score")
  ->from('cms_page p')
  ->orderBy('score DESC')
  ->addComponent('p', 'CmsPage p');

That doesn't work. Any pointers?

Thanks in advance!

Answer 1

According to the MySQL fulltext natural language search docs:

When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first. Relevance values are nonnegative floating-point numbers. Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

Does this not work for you?

You can read more about natural fulltext search in MySQL here.