如何使用Doctrine_RawSql进行全文搜索，并按相关性进行排序 [英] How to use Doctrine_RawSql for a fulltext search and sorting by relevance

问题描述

这段代码将执行搜索：

  $ q = new Doctrine_RawSql（）; 
 
 $ q-> select（'{p。*}'）
  - > from（'cms_page p'）
  - > where（'match .content）对（？）'，$ user_query）
  - > addComponent（'p'，'CmsPage p'）; 
  

这将执行。我希望结果按相关性排序

真正的sql必须看起来像：

从
 cms_page中选择
 p.id，
 match（p.content）对（？）作为
作为p 
订单
得分desc; 
  

所以我需要得到那个匹配...反对条款在选择...我想。

我的crapshoot猜测在完成这个是：

  $ q-对于（'$ escaped_user_query'）作为评分，选择（{p.id}，match（{p.content}）
  - > from（'cms_page p'）
  - > ; orderBy（'score DESC'）
  - > addComponent（'p'，'CmsPage p'）; 
  

这不行。任何指针？

提前感谢

解决方案

根据MySQL全文自然语言搜索文档：

当在WHERE子句中使用MATCH（）时，如前面所示的示例，返回的行是自动排序相关性最高的。相关性值是非负的浮点数。零相关性意味着没有相似性。相关性是根据行中的单词数，该行中的唯一字数，集合中的单词总数以及包含特定单词的文档数量（行）计算的。

这不适合您吗？

您可以阅读更多关于MySQL中的自然全文搜索 here。

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!

解决方案

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.

这篇关于如何使用Doctrine_RawSql进行全文搜索，并按相关性进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！