Fulltext Search

Hi,

On my site now, I have a ‘simple’ search form, where you can search for books. Not only for a title, or description (same table), but also to the related tables (Author, Publisher, …)

Most of the visiters are searching for ‘Book + Title’ and there comes the problem.

What I do is using the searchterm and adding in front and back a ‘%’, and 99% of the visiters don’t get any result.

In my search on Google a found a solution that was related with Fulltext search. Is Yii Framework supporting this?

In attachment I’ve added my search




$condition[] = '(title LIKE :string OR price LIKE :string OR ISBN LIKE :string OR description_user LIKE :string)';


$condition[] = 'is_sold = :is_sold';

$condition[] = 'is_deleted = :is_deleted';


$params[':is_sold'] = 0;

$params[':is_deleted'] = 0;


$criteria = new CDbCriteria;

$criteria->condition = implode(' AND ', $condition);

$criteria->params = $params;

$criteria->with = array('authorlink', 'user', 'publisher');

$criteria->addSearchCondition('authorlink.name', '%' . $searchstring->search_string . '%', false, 'OR');

$criteria->addSearchCondition('publisher.name', '%' . $searchstring->search_string . '%', false, 'OR');

$criteria->addSearchCondition('user.name_first', '%' . $searchstring->search_string . '%', false, 'OR');

$criteria->addSearchCondition('user.name_last', '%' . $searchstring->search_string . '%', false, 'OR');

        

        $items = Item::model()->findAll($criteria);



Yii doesn’t have any built-in search engine but it’s fairly easy to integrate Zend Lucene.

Also, for MySQL databases, there’s full-text search but it has a few drawbacks:

  • requires MyISAM engine

  • has somewhat limited capabilities and may result in poor user experience.

For any other DBMS , check its documentation for a similar functionality.

One thing’s for sure: never use CDbCriteria::addSearchCondition() on a high traffic site. It doesn’t scale really well.

Thx for the explanation!

To test a quick version, I tried this one:




$criteria->select = "MATCH (title) AGAINST ('$searchstring->search_string' IN BOOLEAN MODE)";

$items = Item::model()->findAll($criteria);



But got the whole table as return, instead of 4 items (if I do the SQL in PHPMyAdmin)

What I’m doing wrong?

Can’t test it atm, but I would try:




$criteria->condition = "MATCH (title) AGAINST ('$searchstring->search_string' IN BOOLEAN MODE)";

$items = Item::model()->findAll($criteria);



Thx that works idd !