search on metadata in MANY_MANY relation

Yii 1.1 General Discussion
1

Tables important to this search:




data

  id

metadata

  id

  data

data_metadata

  data_id

  metadata_id

Current Search($query = null) method in de data controller:




$criteria = new CDbCriteria();

$criteria->with = array('metadatas');

$criteria->together = true;

if($query && strlen($query) > 1) {

   $criteria->compare('metadatas.data', $query, true);

}

        

return new CActiveDataProvider($this, array(

   'criteria'=>$criteria,

));

Situations:

$query = null, $together = true: CListView shows 1 entry. Should be 10.

$query = ‘phim’, $together = false: database error, unknown column metadatas.data

$query = ‘phim’, $together = true: 4 items on page instead of 10 not all metadata

Goal:

Obtain DATA + all its METADATA where METADATA.data matches $query in one of the METADATA assigned to DATA.

2

I have never had success trying to add a related table condition the way you have it.

Instead I use:




    $criteria->addCondition("my statement");