Using CGridView Search with relations

Yii 1.1 General Discussion
1

I have a system that logs customer enquiries; these enquiries are assigned to users. I am trying to create a CGridView that displays stats for each user, for example how many enquiries they have worked on daily / weekly / yearly, etc.

I am struggling with this at the moment, I could do with some guidance. I have a relation as follows:

User model:


'total_enquiries_today'=>array(self::STAT, 'Enquiry', 'user_id',

    'condition'=>'started_at > :started_at',

    'params'=>array(':started_at'=>date('Y-m-d')),

),

An enquiry also has a status code, for example: OPEN, CONFIRMED, CLOSED (the field ‘status’ is in the Enquiry model)

The idea is when I use the search form on the page, I can display figures based on the status (dropdown list). For example:

  • number of enquiries set to CONFIRMED status TODAY (for each user)

I am creating the search() function in the User model. The problem I am having is integrating the relation with the search() function (CActiveDataProvider). I have tried using the ‘with’ property but that doesn’t make any difference.

I have put in the following in the User model:


public $status; // at the top


// (in the search function)

$criteria->with('total_enquiries_today');

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

And in my CGridView column I have done this:


array(

    'name'=>'total_enquiries_today',

    'value'=>$model->total_enquiries_today,

),

The search is not taking in the selected ‘status’. The column needs to display the correct value based on the query criteria. I think I may have gone about this in the wrong way.

Bear in mind there will also need to be further relations (and grid columns) to handle other time periods (weekly / monthly / yearly)

2 



//  add the status to your search scenario :

 public function rules()

    {

        return array(

           ..

            array('..status .. ', 'safe', 'on'=>'search'),

        );

    }

  

array(

    'name'=>'total_enquiries_today',

    'value'=>$model->total_enquiries_today,

),


// to this :

array(

    'name'=>'total_enquiries_today',

    'value'=>'$data->total_enquiries_today',

),

read this two wiki Searching and sorting by related model in CGridView AND Searching anSearching and Sorting by Count of Related Items in CGridViewd Sorting by Count of Related Items in CGridView

3

Thanks yiqing95.

That almost works great for me (the second wiki link that you posted), except I am unable to make the ‘status’ dropdown work - I get the error:

Unknown column ‘e.status’ in ‘where clause’.

Here is my new code (User model):




$today_count_sql = "(SELECT COUNT(*) FROM enquiry e WHERE e.user_id = t.id AND started_at > '". date("Y-m-d") ."')";


$criteria->select = array(

	'username',

	$today_count_sql . " AS today_count",

);


$criteria->compare('e.status', $this->status);

I have no idea how to reference the ‘status’ column which is in the Enquiry model.

4

Anyone?