Yii: HAS_MANY search

Question

I have the following tables:

user (id, cv_personal_data_id),
cv_personal_data (id, firstname, surname, gender, address, ...),
cv_laboral_exp (id, user_id, position, seniority,... ),
cv_study (id, user_id, name, institution, average, ...),
cv_language (id, user_id, language_name, writing_level, ...)

In my User model I have defined the following relations:

    public function relations()
{
    return array(
        'cvLaboralExps' => array(self::HAS_MANY, 'CvLaboralExp', 'user_id'),
        'cvLanguages' => array(self::HAS_MANY, 'CvLanguage', 'user_id'),
        'cvStudies' => array(self::HAS_MANY, 'CvStudy', 'user_id'),
        'cvPersonalData' => array(self::BELONGS_TO, 'CvPersonalData', 'cv_personal_data_id'),
}

The problem is: logged in as a Company, I need to display a CGridView listing all the users and being able to search by any of the fields of the related tables, such as 'position' (from cv_laboral_exp), 'language_name' (from cv_languages), and so on. I can't seem to find a solution to search fields that come from a HAS_MANY relation. I tried adding in the search() method of the User class the 'with' statement to the $criteria, in an attempt to search the position of a user laboral experience, but without success:

                $criteria->compare('cvLaboralExps.position',$this->cvLaboralExps,true);
                $criteria->with = array('cvLaboralExps'=>array('select'=>'cvLaboralExps.position','together'=>true)); 

As you see there are a lot of relationships that form the CV of a user. I'd be very grateful if anyone could help me solve this, even if it implies changing the database/model structure.

Answer 1

You'll actually need to declare a member variable for the model in question, here it is User. The problem with what you are doing is this(in compare()) : $this->cvLaboralExps , here cvLaboralExps is just a relation of the class, and not a variable, that can store a value, hence the compare's $value is empty. Check this line, explaining the second parameter $value, in the compare documentation:

If the string or the array is empty, the existing search condition will not be modified.

This can be avoided by declaring a member variable for the model, and modifying the compare() calls to use the new variables.

...
class User extends CActiveRecord{
    // declare the variables that we need
    public $cvLaboralExpsLocal,$cvLanguages,$cvStudies;

    // now put these variables in rules array, so that massive assignment can be done, i.e. safe rule
    public function rules(){
         return array(
              // other rules ...
              array('attributesxyz, cvLaboralExpsLocal, cvLanguages, cvStudies', 'safe', 'on'=>'search')
         );
    }

    // other functions

    // the search can be kept just the way you have in the question but with the new variables
    public function search(){
          // other statements
          $criteria->compare('cvLaboralExps.position',$this->cvLaboralExpsLocal,true);
          $criteria->with = array('cvLaboralExps'=>array('select'=>'cvLaboralExps.position','together'=>true));
    }
}

Notes: 1. Remember to change the _search.php form to accept the new variables.
2. Since this is has_many, you'll have to take care of how the end user is entering the values.