Cdbcriteria Inner Join

Yii 1.1 General Discussion
1

Hi

Below is code for the relations and search function for the Resource model class

public function relations()

{

return array(


'collection' => array(self::BELONGS_TO, 'Collection', 'collection_id'),


'classification' => array(self::BELONGS_TO, 'Classification', 'classification_id'),


'keywords' => array(self::MANY_MANY, 'Keyword', 'resource_keywords(resource_id, keyword_id)'),


    'resourceKeywords' => array(self::HAS_MANY, 'ResourceKeywords', 'resource_id'),


'resourceUrls' => array(self::HAS_MANY, 'ResourceUrl', 'resource_id'),


);

}

public function search($pageSize = null, $showPagination = true)

{

$criteria = new CDbCriteria;





$criteria->compare('id', $this->id);


$criteria->compare('collection_id', $this->collection_id);


$criteria->compare('name', $this->name, true);


$criteria->compare('description', $this->description, true);


$criteria->compare('classification_id', $this->classification_id);


$criteria->compare('file_name', $this->file_name, true);


$criteria->compare('original_filename', $this->original_filename, true);


$criteria->compare('active', $this->active);


$criteria->compare('create_id', $this->create_id);


$criteria->compare('create_time', $this->create_time);


$criteria->compare('update_id', $this->update_id);


$criteria->compare('update_time', $this->update_time);








if(!empty($this->start_date) && !empty($this->end_date))


{


    list($from_month, $from_day, $from_year) = explode("/", $this->start_date);


    list($to_month, $to_day, $to_year)       = explode("/", $this->end_date);





    $from_date = mktime(0, 0, 0, (int)$from_month, (int)$from_day, (int)$from_year);


    $to_date   = mktime(23, 59, 59, (int)$to_month, (int)$to_day, (int)$to_year);





    $criteria->addBetweenCondition('create_time', $from_date, $to_date);


}








if (!empty($this->keywords))


{


    $criteria->together = true;


    $criteria->select   = false;


    $criteria->join = 'INNER JOIN resource_keywords ON resource_keywords.resource_id = t.id';


    $criteria->condition('resource_keywords.keyword_id = $this->keywords');


}








$pageSize   = $pageSize != null ? $pageSize : Yii::app()->user->getState('pageSize', Yii::app()->params['default.pagesize']);





$pagination = $showPagination ? array('pageSize' => $pageSize) : false;








return new CActiveDataProvider(get_class($this), array(


    'criteria' => $criteria,





    'pagination' => $pagination


));

}

Everything works great except the part having to do with the keyword variable.

What do I need to change to get the inner join to work for the keywords variable

Any help on how to get this to work, would be greatly appreciated.

Thanks

Lloyd

2

you can also try with left join/right join or simply where condition.

3

also read this article. for more dtails.

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#join-detail

4

Hi,

Finally got it to work using the following code for the criteria

        if (!empty($this->keywords))


        {


            // force to join resourceKeywords (without selecting)


            $criteria->with = array(


                'resourceKeywords' => array(


                    'select' => false,


                    'together' => true,


                    'joinType' => 'INNER JOIN',


                    'condition' => 'resourceKeywords.keyword_id = ' . $this->keywords,


                ),


            );


        }