Yii 1.1: How to create a criteria condition for a multiple value text search i.e a "LIKE IN"

7 followers

This is a shortcut for adding a text search for multiple values i.e.

... WHERE [other conditions here] AND (name LIKE '%ABC%' OR name LIKE '%RET%' OR name LIKE '%Anything else%') ...

For a scenario where only the name field above is the only condition a single CDbCriteria instance will do i.e

$c1=new CDbCriteria;
 
foreach($search_values as $txt){ 
    $c1->compare('name',$txt,true,'OR');
}

The problem is this will produce the following sql when searching with say a 'type' field too:

... WHERE ((((type=1) OR (name LIKE '%ABC%')) OR (name LIKE '%RET%')) OR (name LIKE '%Anything else%')) ...

One solution is to ensure that your condition for the search on name appears first such that the sql produced will read

... WHERE ((((name LIKE '%ABC%') OR (name LIKE '%RET%')) OR (name LIKE '%Anything else%')) AND (type=1)) ...

For cases where this isn't possible, another solution is to create another CDbCriteria instance to handle this specific condition and use CDbCriteria::mergeWith() to combine the two CDbCriteria instances as follows (thanks to Peter Jk's comment).

$c1=new CDbCriteria; // original criteria
$c2=new CDbCriteria; // criteria for handling our search values
 
foreach($search_values as $txt){ 
    $c2->compare('name',$txt,true,'OR');
}
 
$c1->mergeWith($c2); // Merge $c2 into $c1

You can also use CDbCriteria::addCondition() to manually add it to the original criteria as follows:

$c1=new CDbCriteria; // original criteria
$c2=new CDbCriteria; // criteria for handling our search values
 
foreach($search_values as $txt){ 
    $c2->compare('name',$txt,true,'OR');
}
 
$c1->addCondition($c2->condition);
$c1->params+=$c2->params; // Add the params for $c2 into $c1

Total 2 comments

#17808 report it
Yatin Mistry at 2014/07/24 08:29am
mergeWith Works

check also this link its works thanks Peters JK Click here...

#16125 report it
Peter JK at 2014/01/21 11:44pm
mergeWith

the easy way you can use mergeWith. mergeWith support AND and OR condition...

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#mergeWith-detail

Leave a comment

Please to leave your comment.

Write new article
  • Written by: toph
  • Category: How-tos
  • Yii Version: 1.1
  • Votes: -1
  • Viewed: 16,090 times
  • Created on: Jan 20, 2014
  • Last updated: Aug 3, 2014
  • Tags: search, like, in, cdbcriteria