Paginating with Fulltext Searches (Articles) | The Bakery, Everything CakePHP

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

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);

?>

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.

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?

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

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.

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

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).

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.

The Bakery Everything CakePHP