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

You are viewing revision #4 of this wiki article.
This version may not be up to date with the latest version.
You may want to view the differences to the latest version or see the changes made in this revision.

« previous (#3)next (#5) »

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

[sql]

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

[sql]

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

[sql]

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

0 0
5 followers
Viewed: 54 666 times
Version: Unknown (update)
Category: How-tos
Written by: toph
Last updated by: toph
Created on: Jan 20, 2014
Last updated: 7 years ago
Update Article

Revisions

View all history

Related Articles