CakePHP MySQL Full Text Search

Question

I read that for ranking search results you can query MySQL as follows:

SELECT * , MATCH (title, body) AGAINST ('$search') AS rating FROM posts WHERE MATCH (title, body) AGAINST ('$search') ORDER BY rating DESC 

Is there a way to do this in CakePHP 2.X? In addition, I need to do this while paginating the pages. Therefore, I think that I will need to write a condition for the paginator, and not a direct request.

Thank you for your help!

Answer 1

Well, it took me a while ... Since the key issue was getting the ranking for the total matches, the hard part of this query was a certain field:

MATCH (title, body) AGAINST ('$search') AS rating

I decided that I just had to write this field in the "field" option in the pagination array. The result is the following:

  $this->paginate = array( 'limit' => 15, 'fields' => array('*', "MATCH (data) AGAINST ('$q') AS rating"), 'conditions' => "MATCH(SearchIndex.data) AGAINST('$q' IN BOOLEAN MODE)", 'order' => array( 'rating' => 'desc', ), ); $paginatedResults = $this->paginate('SearchIndex'); 

And it worked without a problem!

I think this is the best way to achieve real search results with Cake. If someone has no better alternative :)

Search phrases between double quotes will give you the results you should expect!

Answer 2

Use so that it also prevents mysql injection

 array("MATCH(User.current_position) AGAINST(? IN BOOLEAN MODE)" => $srch_arr['text']) 

Answer 3

I used the above database call by Thomas (thanks) and it works without any problems.

However, the code:

 'conditions' => "MATCH(SearchIndex.data) AGAINST('$q' IN BOOLEAN MODE)", 

removes the data abstraction layer and opens your site for SQL injection.

This is probably not so good (not fully tested), but try:

 'SearchIndex.data LIKE'=>'%'.$search.'%' 

I hope this will be useful anyway.