Getting Related Data With Search

Yii 1.1 General Discussion
1

Hi all,

I’m trying to do something that appears to be common and straight forward, but all the other questions around this issue either deal with some sort of special case. I spent a couple of hours reading and testing to no avail. So here’s the problem…

I have two business objects: a Thing, and a Status. A Thing belongs to a Status and a Status has many Things.

This is modelled in Yii with two models, and the relationship is defined in the Thing model:




'currentstatus' => array(self::BELONGS_TO, 'Status', 'status_id'),

Some of the Status have a property which mean they are ‘active’ and some ‘inactive’. To tell if a Thing is ‘active’ we have to look at the related status to see which type it is.

The use case I’m trying to solve is that we want to look at a grid view of Things and select only ‘active’ things.

In the controller I have the action:


 

	public function actionReportActive()

	{

		$model=new Thing('search');

		$model->unsetAttributes();  // clear any default values

           //   ## Some criteria here ? ? 

		$this->render('reportactive',array(

			'model'=>$model,

		));

	}

and then the $model is rendered in the ‘reportactive’ view using a TBgridView.




 $this->widget('bootstrap.widgets.TbGridView', array(

    'dataProvider'=> $model->search(),

// ## some criteria here ? ?

    'filter'=>$model,

    'columns'=>array(

        'name',

        'size',

        'currentstatus.name',

        'currentstatus.active',

       array(

            'class'=>'CButtonColumn',

        ),

    ),

));

As you can see from the two snippets, I have experimented with putting criteria in both the search definition in the controller and the grid view. I can make this work either way with simple searches based on the Thing properties, but I cannot work out how to say "get only the Things that have a currentstatus.active = 1.

I feel there must be an easy solution to this, as its a very common case. Can someone show me how to do it?

thanks

2

Hi my friend

You could add in your ‘Thing’ model


public $active;

and in search method of the same Model add this


$criteria->with = array('currentstatus');

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

or if you want to show only the active records you have to


$criteria->with = array('currentstatus');

$criteria->compare('currentstatus.active',1);
3

Thanks for the quick reply - I had tried this but got the following error. BTW Thing is actually ‘sample’ and Status is ‘sample_status’:




CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found:

1054 Unknown column 'currentstatus.active' in 'where clause'.

The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`id`) FROM `sample` `t` LEFT OUTER JOIN `sample_status` `currentstatus` ON (`t`.`status`=`currentstatus`.`id`) WHERE (currentstatus.active=:ycp0)
4

OK Solved!

I just changed your line

$criteria->compare(‘currentstatus.active’,1);

with

$criteria->compare(‘active’,1);

and it works.

5

So… Follow on question.

Using this method now all views using search have this constraint on them. I’d rather put the constraint in the view, so that the search() function can be used in the admin view without creating two search functions in the controller. How could that be done?

6

because the search model method returns new CActiveDataProvider($this,…) you have already fetch the data.

So, the best practice is to create another one search method (searchAdmin)

so according to the permissions you have to call


$model=new Thing('search');

or

$model=new Thing('searchAdmin');

and in view file


 $this->widget('bootstrap.widgets.TbGridView', array(

    'dataProvider'=> $model->search(),

or

 $this->widget('bootstrap.widgets.TbGridView', array(

    'dataProvider'=> $model->searchAdmin(),

Another way is to check the permissions into the search model method, but this practice in not good idea according to MVC architecture

7

According to the error the active attribute belongs to the ‘Thing’ model, so the the right compare is $criteria->compare(‘active’,1) indeed

8

OK, I see your point. Thanks for that.

9

One last thing - in the alternative format you suggested:

$criteria->with = array(‘currentstatus’);

$criteria->compare(‘currentstatus.active’,$this->active);

where/how do I set the $this->active to be 1?

10

In controller action, assign value to "active"


public function actionReportActive()

{

    $model=new Thing('searchactive');

    $model->unsetAttributes();  // clear any default values

    $model->active=1;

    //   ## Some criteria here ? ? 

    $this->render('reportactive',array(

            'model'=>$model,

    ));

}

In model, Retrieve "active" value


public function searchactive()

{

$criteria=new CDbCriteria;

$criteria->with = array('currentstatus');

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


return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

  }
11

Awesome - thanks, that makes sense.