Criterion's solution is the one I use. I just replicate the search data to a MyISAM table on save. Why do you say it's a bad solution? It's fairly standard to store fulltext search data in a MyISAM table and use something (imo) better like InnoDB for the transaction support etc.
I wrote a small FullTextSearchable ModelBehavior if it's of any use:
<?php
/**
* FulltextSearchableBehavior
*
* Allows a fulltext search to be performed against the Model.
*
* This behaviour allows a model to be searched using fulltext boolean searches.
* By default, this takes place by adding an element to the $options array passed
* to find which contains the query string. By default, this option is named 'fulltext'.
* The presence of this key indicated that a search should take place, and only
* data which matches the query string will be returned.
*
* There are some restrictions to this behaviour, primarily that fulltext search
* is only available for models which use a MySQL database and MyISAM table.
* To avoid slow queries, it is also a very good idea to build FULLTEXT indeces on
* the searchable fields. The following SQL can be used for this:
*
* ALTER TABLE table_name ADD FULLTEXT(field)
*
* The searchable fields should be passed in to the behaviour configuration as
* an array, with a integer weighting value as key, and the field name as value.
* The weighting value is used to give precedence to certain fields. For example,
* finding a keyword in a title field is usually more significant that finding
* in a content field. It is suggested to use factors of 10 for weighting keys,
* for example 1, 10, 100, 1000 etc.
*
* To fine tune the search, you need to edit the mysql config file /etc/mysql/my.conf.
* The following keys under the [mysqld] section are useful:
*
* ft_min_word_len
* ft_stopword_file
*
* ft_min_word_len is the minimum length a word has to be before it will be indexed,
* and therefor included in search results. By default this is 4 characters, but
* this has been lowered to 3 for the purposes of the CMS.
* ft_stopword_file contains the path to a file containing words which should be
* excluded from the search process. For example 'and' should usually be ignored.
* If this option is not set, mysql uses a built in stopwords list. For the purposes
* of the CMS, the list has been disabled altogether, so nothing is excluded. After
* changing either of these settings, mysql should be restared, and the following
* query run to rebuild the search indeces:
*
* REPAIR TABLE table_name QUICK
*
* The following operators are accepted in the query string:
* + The word is mandatory in all rows returned.
* - The word cannot appear in any row returned.
* < The word that follows has a lower relevance than other words, although
* rows containing it will still match
* > The word that follows has a higher relevance than other words.
* () Used to group words into subexpressions.
* ~ The word following contributes negatively to the relevance of the row (which
* is different to the '-' operator, which specifically excludes the word, or
* the '<' operator, which still causes the word to contribute positively to
* the relevance of the row.
* * The wildcard, indicating zero or more characters. It can only appear at the
* end of a word.
* " Anything enclosed in the double quotes is taken as a whole (so you can
* match phrases, for example).
*
*
*/
class FulltextSearchableBehavior extends ModelBehavior {
private $_queryKey = 'fulltext';
public function setup($model, $config = array()) {
if(!empty($config)) {
if (array_key_exists('fields', $config)) {
$this->settings[$model->alias]['fulltext_fields'] = (array) $config['fields'];
}
}
}
/**
* Inspects the search config looking for a key called $this->_queryKey. If set,
* the value of the element with this key is taken to be the search query string,
* and the search is applied as part of the query.
*
* @param object $model A reference to the model to which this behaviour is attached.
* @param mixed $config The $options array as passed to the Model::find method.
* @return mixed The modified $options array which now includes the relevant info
* for performing the search.
*/
public function beforeFind($model, $config) {
if (empty($this->settings[$model->alias]['fulltext_fields'])) {
// no searchable fields defined
return $config;
}
if (!array_key_exists($this->_queryKey, $config)) {
// no query string provided
return $config;
}
foreach ($this->settings[$model->alias]['fulltext_fields'] as $weight => $field) {
$rating[] = sprintf(
"(MATCH (`%s`.`%s`) AGAINST ('%s' IN BOOLEAN MODE) * %d)",
$model->alias, $field, $config[$this->_queryKey], $weight
);
$whereFields[] = sprintf("`%s`.`%s`", $model->alias, $field);
}
$config['fields'] = (array) $config['fields'];
if (empty($config['fields'])) {
array_unshift($config['fields'], '*');
}
array_push($config['fields'], '(' . implode(' + ', $rating) . ') AS `rating`');
$config['conditions'] = (array) $config['conditions'];
array_unshift(
$config['conditions'],
sprintf(
"MATCH (%s) AGAINST ('%s' IN BOOLEAN MODE)",
implode(',', $whereFields), $config[$this->_queryKey]
)
);
$config['order'] = (array) $config['order'];
array_unshift($config['order'], '`rating` DESC');
return $config;
}
}
?>
Usage is pretty simple:
public $actsAs = array(
'FulltextSearchable' => array(
'fields' => array(
100 => 'title',
10 => 'description',
1 => 'notes'
)
)
);
I add the following to AppController to handle searches automatically (though this is not necessary to use the behavior:
function beforeFilter() {
if ($this->_userSearched()) {
$this->__handleSearch();
} else {
$this->set('searched', false);
}
}
/**
* Provides a globally available search mechanism. Any view can build a form
* which includes a hidden Search.action field and a Search.query textbox. When
* submitted, this method picks up the data and redirects to the specified action
* (or the referring action if none specified, or index of the referring action can't
* be determined). The redirection causes the __handleSearch method to be called.
*/
function search() {
// if no redirect action was specified, use the referring action
if (!isset($this->data['Search']['action'])) {
$route = Router::parse($this->referer());
if (!empty($route['action'])) {
$this->data['Search']['action'] = $route['action'];
} else {
$this->data['Search']['action'] = 'index';
}
}
$url['action'] = $this->data['Search']['action'];
if (isset($this->data['Search']['passedParams'])) {
foreach ($this->data['Search']['passedParams'] as $param) {
$url[] = $param;
}
}
$url['search'] = $this->data['Search']['keywords'];
$this->redirect($url, null, true);
}
protected function _preHandleSearch() {
$this->data['Search']['keywords'] = $this->passedArgs['search'];
$this->set('searched', $this->data['Search']['keywords']);
}
function _userSearched() {
return isset($this->passedArgs['search']);
}
/**
* Handles the search performed by the user.
*
* By default, this method handles full text search, and simple searches for
* the model's id. If the search is to be performed
* via FullTextSearchable behaviour, nothing more is to be done (aside from configure
* the behaviour in the relevant model). However this method can also be overridden
* in any controller in order to implement a custom search. The overriding
* method should populate the paginate property for the particular searched model.
* The overriding method should also be sure to call the _preHandleSearch()
* method of app_controller.
*/
function __handleSearch() {
$this->_preHandleSearch();
if (is_numeric($this->passedArgs['search'])) {
// assume the search was for an id
$search = array(
'conditions' => array(
$this->{$this->modelClass}->alias . '.id' => $this->passedArgs['search']
)
);
} else {
// assume this is to be a fulltext search
$search = array(
'fulltext' => $this->passedArgs['search']
);
}
$this->paginate[$this->{$this->modelClass}->alias] =
array_merge_recursive($this->paginate[$this->{$this->modelClass}->alias], $search);
}
