Searching with CDbCriteria

Question

Is there any way to make a CDbCriteria search (as in compare()) in the fields I'm selecting, but using the model's search() method instead of having to manually add the compare() conditions?

Note that I'm aiming at a solution that will let me write some fewer lines, nothing more and nothing less. So, if the solution is something really hacky and/or mesy, I'll just go for the "add-a-few-compares()" method.

My current code:

$criteria = new CDbCriteria;
$criteria->with = array('A', 'B', 'C', 'D', 'E');

$criteria->compare("A.field1", "test", false, 'OR');
$criteria->compare("A.field2", "test", false, 'OR');
$criteria->compare("B.field1", "test", false, 'OR');
$criteria->compare("B.field2", "test", false, 'OR');

$dataProvider = new CActiveDataProvider('Z', array(
    'criteria'=>$criteria,
    //pagination...
    //more options...
));

Solution

Update: It seems that you are actually looking(from comments below this answer) for partial matches, and for that you will have to pass true to your compare calls:

$criteria->compare("A.field1", "test", true, 'OR');

Even that can be passed to addCondition:

$criteria->addCondition('A.field1 LIKE "%test"','OR');
// or with params as below
$criteria->addCondition('A.field2 LIKE :test','OR');
$criteria->params=array(
    ':test'=>'%test%',
);

---

As i have already mentioned in the comments, i don't think it'll be possible to use each model's default search() method. There are other alternatives though, for instance you can use addCondition instead:

$criteria = new CDbCriteria;
$criteria->with = array('A', 'B', 'C', 'D', 'E');
$criteria->together = true; // you'll need together so that the other tables are joined in the same query

$criteria->addCondition('A.field1 = "test"','OR');
$criteria->addCondition('A.field2 = "test"','OR');
// and so on

I would suggest going with the above, because compare (doc-link) should actually be used in cases when you want to "intelligently" determine the operator for comparision, for example: if you are taking the test values from user input and the user is allowed to use operators (<,>,<= etc). After determining the operator to be used in the condition, compare calls other functions accordingly, including addCondition. So using addCondition will atleast avoid those unnecessary checks.

Further if all you have to do is check equality only, i.e if your sql's WHERE is supposed to be:

WHERE A.field1 = "test" OR A.field2 = "test"

then you don't even need addCondition, and you can simply use a more complex condition (doc) :

$criteria->condition='A.field1 = "test" OR A.field2 = "test"';
// or even better if you use params
$criteria->condition='A.field1 =:test1 OR A.field2 =:test2 OR B.field1 =:test3 OR B.field2 =:test3';
$criteria->params=array(
    ':test1'=>'test',
    ':test2'=>'anothertest',
    'test3'=>'tests' // omitting ':' here for params also works
);

Comments

[bool.dev]

it seems to me from your current code, that you are only checking for equality. for that condition and addCondition are better options, so do follow up.

[alexandernst]

I'm actually working on a 'search' field, so WHERE looks more like field1 like '%something%'.... I was wondering if there is a way to make Yii compare the strings, instead of making MySQL compare them. I'm asking this because this way fuzzy string matching should be possible. Because MySQL doesn't support fuzzy string matching, right? Even further, disordered string match, aka "this is a test" == "a test this is"

[bool.dev]

ok, but to get like %something%, you'd have to pass true in the compare, but you are passing false, which made me assume you are only checking for = something. Anyway, by fuzzy string matching if you mean with a regexp, then mysql does support that (although i haven't had to do anything like that yet -sadly). And of course for disordered string matching you'd need like and the %, and for that you'd definitely need to pass true to compare.

[alexandernst]

fuzzy string matching means matching strings that are almost equal, e.g. "This is a test" == "This si a tset". About the disordered matching, how is like and % supposed to help me? "This%is%a%test" won't match "a%test%this%is".

[bool.dev]

oh sorry, i thought you wanted to match a string that contains "test" instead. anyway, i gave example of like '%something%' because you said that's what you are searching for, in your previous comment. for fuzzy you can use regexp, disordered i'm not sure

[alexandernst]

Thank you, bit but your edit is just matching every field against each "word" from the user input. This is not precise at all, and won't work in any real case. In my example, matching "%a%" agains whichever field will almost always return true, which, combined with the OR's, will lead to a sql result containing almost everything from my database. Going even further, comparing "This is a test" (4 words) agains 10 fields will make MySQL do 40 compare operations per row. I'm really aiming at making Yii perform the compare, instead of MySQL.

[bool.dev]

let us continue this discussion in chat