Yii 1.1: Use CGridview to sort and filter CSqlDataProvider - when used with UNRELATED tables (or complex queries)

3 followers

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

Total 6 comments

#18797 report it
Gerhard Liebenberg at 2015/01/10 06:41am
Suggestion for code cleanup

Thanx Bass28

#18790 report it
Bass28 at 2015/01/09 09:37am
Suggestion for code cleanup

I do something very similar to this and would only offer one suggestion. The code

if( ($this->gv_hospitals !== null) && 
        ($this->gv_hospitals !== ''))

is not needed. CDBCriteria will handle null parameters and you will end up with the condition WHERE statement = ''. If you would rather not have this type of WHERE statement just separate the it out like this

if ($criteria->condition != '') {
     $command->where($criteria->condition, $criteria->params);
}

This does not fix any issues with the code but just makes the code easier to maintain because you do not have the duplicate SQL statement.

#18431 report it
Gerhard Liebenberg at 2014/10/28 10:35am
Experiment

Hi Le-top, I changed the sql to prevent injections.

#18421 report it
le_top at 2014/10/27 02:46pm
Experiment

You may have to experiment a bit and possibly indicate another primary key-value.

Regarding the edit, view, delete links: you surely have to personnalise them and include the keys in the generated url. Still, that is less error prone and more flexible than constructing the SQL request yourself. Your example does not mind about SQL injection for instance which you would get protection from automatically when using the usual way.

I haven't looked into it, but I suppose it is possible.

I think it is worth experimenting it because of the potential benefit you get.

#18418 report it
Gerhard Liebenberg at 2014/10/27 01:26am
relation with empty join condition

Hi Le_top. That sounds interesting.

Will 'keyField' still have the required composite key - which it needs to make some gridview js functions return unique keys?

Will you still use CSqlDataProvider?

#18417 report it
le_top at 2014/10/26 07:46pm
relation with empty join condition

Isn't it possible to set up a relation with an empty join condition. I needed a condition which is '1=1'.

That way all Yii functionnality would still work, including RelatedSearchBehavior.

Leave a comment

Please to leave your comment.

Write new article