I have two tables called hospitals and departments. I have a junction table between them which holds the departments in each hospital. But sometimes I need a list of "All hospitals with All possible departments" - regardless of whether they are linked in the junction table or not.

So I need to read all hospital records and "cross join" each of them with all department records - bypassing the junction table. Obviously in this case, hospitals and departments are unrelated tables.

The default order in the CGridView is hospitals ASC, departments ASC. In the CGridView, I want to be able to sort and filter hospitals only.

Model:

public $gv_hospitals = null;

public function rules()
{
	return array(
		...
		array('gv_hospitals', 'safe', 'on'=>'search'),
	);
}

public function search()
{
	$count=Yii::app()->db->createCommand(
		'SELECT COUNT(*) FROM
		tbl_hosp, tbl_dept')->queryScalar();
	
	// If gv_hospitals was received from CGridview, then
	// filter records with a WHERE clause
	if( ($this->gv_hospitals !== null) && 
		($this->gv_hospitals !== ''))
	{
		$criteria	= new CDbCriteria;
		$criteria->compare('hosp_name',$this->gv_hospitals, true);
		$condition	= $criteria->condition;
		$params		= $criteria->params;
		unset($criteria);
		 
		$sql = Yii::app()->db->createCommand()
		->select("
		    	concat(tbl_hosp.hosp_id, tbl_dept.dept_id) AS compkey,
		        tbl_hosp.hosp_id    AS hosp_id,
		        tbl_hosp.hosp_name  AS hosp_name,
		        tbl_dept.dept_name  AS dept_name
		")
		->from("tbl_hosp, tbl_dept")
		->where($condition, $params);
	}
	else
	{
		$sql = Yii::app()->db->createCommand()
		->select("
		    	concat(tbl_hosp.hosp_id, tbl_dept.dept_id) AS compkey,
		        tbl_hosp.hosp_id    AS hosp_id,
		        tbl_hosp.hosp_name  AS hosp_name,
		        tbl_dept.dept_name  AS dept_name
		")
		->from("tbl_hosp, tbl_dept");
	}
	
	return new CSqlDataProvider($sql, array(
		
		// Use the composite key to keep the (hidden) key values of your
		// gridview rows unique,
		// because functions like getChecked() return key values of
		// checked ROWS - not the id or value of the checkboxes.
		'keyField' => 'compkey',
		
		'totalItemCount'=>$count,
		
		'sort' => array(
			
			// Indicate what can be sorted
			'attributes' => array(
				'gv_hospitals'=>array(
				 	 'asc' =>'hosp_name ASC,  dept_name ASC',
		 	 	 	 'desc'=>'hosp_name DESC, dept_name ASC',
			  	), 
			  	'dept_name',
			),
			
			// Default order in CGridview
			'defaultOrder' => array( 
				'gv_hospitals' => CSort::SORT_ASC,
				'dept_name' => CSort::SORT_ASC,
			),
		),
		
		'pagination'=>array(
			'pageSize'=>$count, //Show all records
		),
	));	
}

CGridView data columns:

array(
	
	array(
		'header'=> 'Hospitals',
		'name'	=> 'gv_hospitals',
	    'value'	=> '$data["hosp_name"]',
		'filter'=> CHtml::activeTextField($model, 'gv_hospitals'),
	),
	    
    array(
        'header' => 'Departments',
        'value'=>'$data["dept_name"]',
    ),
),

Cheers