How do I configure my Yii model for relational queries?

Question

A bit new to yii and have been having trouble trying to do a join query in my gii-generated model.

Summary:
I want to return videos (table 'videos') that have met specific search criteria. To do this, I have my 'videos' table, and I have another table 'searchmaps'. All searchmaps does is associate a video_id to a search_id so that I can keep track of multiple videos that met criteria for a single search scenario..

What I've tried:
I tried following yii docs for relational queries but I guess I'm missing something still... Below is my code. What am I doing wrong??

(Note: I wish to return a model using CActiveDataProvider)

Tables:

CREATE TABLE IF NOT EXISTS `videos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `directory` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `category` int(2) NOT NULL,
  `tags` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `filetype` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `duration` int(11) NOT NULL,
  `status` int(1) NOT NULL,
  `error` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=17 ;


CREATE TABLE IF NOT EXISTS `searchmaps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `search_id` int(11) NOT NULL,
  `video_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=69 ;

Classes:

Here is the Controller class:

//From VideosController.php

 ...

public function actionIndex($searchmap_id)
{
    $dataProvider = new CActiveDataProvider('SearchVideos', array(
            'criteria' => array(
        'with' => array('search.video_id','search.search_id'),
        'together' => true,
            'condition'=>'videos.id = search.video_id AND search.search_id='.$searchmap_id,
                )));
        $this->render('index',array(
        'dataProvider'=>$dataProvider,
        ));
}

Below is the main model class:

// From Videos.php

...

/**
* @return array relational rules.
*/

public function relations()
{
   // NOTE: you may need to adjust the relation name and the related
   // class name for the relations automatically generated below.
   return array(
   'search'=>array(self::BELONGS_TO, 'Searchmaps', 'video_id'),
           );
}

Here is the model class of the related table

// From Searchmaps.php

   ...

/**
* @return array relational rules.
*/

public function relations()
{
  // Each row has a search_id and a video_id relating to a specific video
  // Multiple rows may have different videos but share the same search_id
   return array(
    'video'=>array(self::HAS_ONE, 'Videos', 'video_id'),
    );
}

Answer 1

First, I would suggest using InnoDB tables so you can set up proper foreign keys -- if you do this then gii will generate the basic relations for you. If you can convert your tables, then you can add the fk with:

ALTER TABLE `searchmaps`
ADD CONSTRAINT `searchmaps_ibfk_1` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

Your relations don't look quite right, seems like they should be:

in Videos model:

return array(
    'searchmaps' => array(self::HAS_MANY, 'Searchmaps', 'video_id'),
);

in Searchmaps model:

return array(
    'video' => array(self::BELONGS_TO, 'Videos', 'video_id'),
);

then your dataProvider can look something like:

$dataProvider=new CActiveDataProvider('Videos',array(
    'criteria'=>array(
        'with'=>'searchmaps',
        'together' => true,
        'condition' => 'searchmaps.search_id='.$search_id,
    )
));

to try it you can output a simple grid in your view with something like:

$this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'videos-grid',
    'dataProvider'=>$dataProvider
));

Again, I would highly recommend using foreign keys in your table and view the relations gii outputs and once you understand what it's doing, it will be much easier to customize. Also, using foreign keys will insure the relationships are maintained. You can use a tool like MysqlWorkbench or similar if you need help creating the foreign keys.