Paginating with Fulltext Searches

By Brit Gardner (britg)
This a short tutorial on how to implement pagination with MYSQL full text searches. Requirements to use this implementation are CakePHP 1.2 and Mysql 3.23.

Let's say you want to do a full-text search on a Mysql (MyISAM) table called `listings` with a fulltext index on the fields `title` and `description`. Your search term comes from a search form that an end-user fills out. You also want to paginate the search results with the incredibly powerful and easy-to-use CakePHP paginator.


Controller Class:

Download code <?php 
# query comes from GET request parameter 'q'
$input $this->params['url']['q'];

# sanitize the query
App::import('Sanitize');
$q Sanitize::escape($input);

# we are searching a table called 'listings'
$options['conditions'] = array(
   
"MATCH(Listing.title,Listing.description) 
          AGAINST('$q' IN BOOLEAN MODE)"
);

$this->set(array('results' => $this->paginate('Listing'$options)));
?>

Voila! You can now handle the $results in your view just as you would normally handle a paginated variable.

 

Comments 727

CakePHP Team Comments Author Comments
 

Comment

1 Some notes

1. In Cake 1.2 you should use App::import() rather than uses()

2. Sanitize methods can be called statically, you don't need to make an instance

3. Sanitize::escape may be more appropriate than Sanitize::clean. The latter will performa a number of transformations on your data by default - such as encoding HTML characters and stripping carriage returns. Unless the data you are searching has been similarly modified, then you may miss matches.

4. I can't see the point of the '1'=>'1 AND' part of the condition - just use a string.

PHP Snippet:

<?php 
# query comes from GET request parameter 'q'
$input $this->params['url']['q'];

# sanitize the query
App::import('Sanitize');
$q Sanitize::escape($input);

# now the pagination options hack to perform a fulltext search
# remember we are searching a table called 'listings'
$options['conditions'] = array(
    
"MATCH(Listing.title,Listing.description) AGAINST('$q' IN BOOLEAN MODE)"
);

$this->set('results' => $this->paginate('Listing'$options);
?>
Posted Jul 8, 2008 by Grant Cox.
 

Comment

2 Thanks

Grant, thanks - I've been overlooking the fact that Sanitize methods can be called statically!

For some reason, without the '1' => '1...' the query generation was simply not working for me. I did not investigate, simply tried this and it worked. I will try to find some time to reproduce or try your method in the near future and update the article.
Posted Jul 17, 2008 by Brit Gardner
 

Comment

3 not working for me...

it seems this code:
$this->set('results' => $this->paginate('Listing', $options));

generates this error:
Parse error: syntax error, unexpected T_DOUBLE_ARROW

when i change the line to:
$this->set('results', $this->paginate('Listing', $options));

nothing gets displayed in the browser...

any ideas what's wrong?
Posted Aug 3, 2008 by Eric
 

Comment

4 this worked for me

I'm pretty new to Cake but I found a different way to get the code working which was submitted by Brit.

Controller Class:

<?php      function index() {

        
$this->Line->recursive 1;
        
$conditions = array();
        
        if (isset(
$this->params['url']['q'])) {
        
            
$input $this->params['url']['q'];
            
            
App::import('Sanitize');
            
$q Sanitize::escape($input);
            
            
$conditions = array(" MATCH(Listing.title,Listing.description) AGAINST('$q' IN BOOLEAN MODE) ");
        }
    
        
$this->set('results'$this->paginate('Listing'$conditions));
         
    } 
?>
I'm not sure if it's the best way, but it seems to work fine.
I've eliminated the awkward '1' => "1 snippet
This is using cake_1.2.0.7296-rc2 & php5

Eric
Posted Aug 3, 2008 by Eric
 

Comment

5 Fixed some errors

I've updated the tutorial to implement the syntax error in #3 and to remove the 'hack' that does not appear to be necessary anymore.
Posted Aug 4, 2008 by Brit Gardner
 

Question

6 Joins for query?

Hi all,

thanks for that great article.

I wonder if it's possible to use "more advanced" (not really) conditions to also search through the associated models (like joining the associated models and query sth like where Listing.title LIKE %test% OR OtherModel.description LIKE %test%)?

Thanks for your help!
Lasse

Posted Nov 27, 2008 by Lasse Rheingans
 

Comment

7 another slightly different variant that works for me

Another variant that I use with 1.2.0.7962 final and php5 is inspired by
http://book.cakephp.org/view/73/Retrieving-Your-Data#Complex-Find-Conditions-74
I use $conditions as an array and fill it with my subsequent required conditions as needed.
For the full text search, I'm using this pattern:

$conditions['MATCH(Listing.title,Listing.description) AGAINST(? IN BOOLEAN MODE)'] = $q;

Filling the array this way should allow "CakePHP to properly escape each individual part of the query." (according to the above mentiond document).
Posted Jan 8, 2009 by Lars Schenk
 

Bug

8 Ordering results by relevance

Has only one bug in this. T To results being order by relevance, you need to add an order conditions like your MATCH...AGAINST expression followed by DESC.
Posted May 5, 2009 by Jonh Petter