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 !== ''))
	{
		// The LIKE operator in the WHERE clause acts the same
		// as this line in Relational Query:
		// $criteria->compare('hosp_name',$this->gv_hospitals,true);
		$sql="
		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
		tbl_hosp.hosp_name LIKE '%" . $this->gv_hospitals . "%'";
	}
	else
	{
		$sql="
		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