Creating a parameterized LIKE query Follow @yiiframework

It's common to see users wishing to make substring DB queries, using the % metacharacter used to match anything; in this FAQ we'll search the tbl_comments table from the blog tutorial hoping to find the text in $match in the content column

A frequently seen yet incorrect effort:

$q = new CDbCriteria( array(
    'condition' => "content LIKE '%:match%'",      // DON'T do it this way!
    'params'    => array(':match' => $match)
) );
 
$comments = Comments::model()->findAll( $q );     // finds nothing

fails to find anything. And the version without single quotes around the :match yields an SQL error.

This search is properly achieved by moving the % metacharacters into the bound parameter:

$match = addcslashes($match, '%_'); // escape LIKE's special characters
$q = new CDbCriteria( array(
    'condition' => "content LIKE :match",         // no quotes around :match
    'params'    => array(':match' => "%$match%")  // Aha! Wildcards go here
) );
 
$comments = Comments::model()->findAll( $q );     // works!

Just as in any other prepared statement, the :match in the condition should not be surrounded by quotes - Yii handles this parameter binding properly.

Alternate versions: ¶

$match = addcslashes($match, '%_'); // escape LIKE's special characters
 
// directly into findAll()
$comments = Comments::model()->findAll(
    'content LIKE :match',
    array(':match' => "%$match%")
);
 
// merging in a parameterized scope:
$this->getDbCriteria()->mergeWith( array(
    'condition' => 'content LIKE :match',
    'params'    => array(':match' => "%$match%")
) );

Better: use CDbCriteria::addSearchCondition() ¶

If the query uses a direct CDbCriteria object, it's more efficient (and just as safe) to do a simple substring match with the CDbCriteria::addSearchCondition() method, which handles it all directly.

$q = new CDbCriteria();
$q->addSearchCondition('content', $match);
 
$comments = Comments::model()->findAll( $q );

Yii will turn this criteria into a LIKE query using % wildcards before and after the match variable. By default, it protects active characters in the variable.

One could also use $q->compare('content', $match, true) but it has a few side effects. For instance, if $match is "<>hello", the criteria will find the rows where the content is NOT LIKE '%hello%'.

Warning ¶

The pattern LIKE '%XXX%' must not be abused.

• This is not scalable and can be really slow on medium-sized databases.
  For instance, MySQL cannot use indexes with such criteria, so it has to process the whole data.
• This is not a google-like search, and complex queries like zend -framework "php core" are not possible. Users often expect more than just a substring search.

While both Postgresql and MySQL include an embedded full-text search, this kind of search is often handled by another service. The most well-known free systems are Xapian, Sphinx and Lucene.

There are already some yii extensions for this:

• Sphinx : DGSphinxSearch Extension
• Solr (Lucene) : Solr Extension