Filter and search by virtual attribute

Hi all,

in the model ‘myModel’ i created a virtual attribute by doing


private $_usedInRelation = null;


        public function getUsedInRelation () {

            if ($this->relationName){

                $this->_usedInRelation = 1;

            }else{

                $this->_usedInRelation = 0;

            }

            return $this->_usedInRelation ;

        }


        public function setUsedInRelation ($value) {

            $this->_usedInRelation = $value;

        }

this model has a relation called "relationName"

I putted ‘usedInRelation’ in model’s rules for search and input.

New attribute is showed in a grid with


'usedInRelation '=>array(

                'header'=>'used in relation?',

                'name'=>'usedInRelation ',

                'type'=>'raw', 

                'value' => '$data->usedInRelation ?Yii::t(\'app\',\'Yes\'):Yii::t(\'app\', \'No\')',

                'filter' => array('0' => Yii::t('app', 'No'), '1' => Yii::t('app', 'Yes')),

                'htmlOptions' => array('style' => "text-align:center;"),

                ),

The problem is that this column is not filterable and searchable

How should i change the dataprovider im myModel?

If I understood right what is your question, U want to show if model does/doesn’t have relationRecord ?

Thank you Dragan.

Yes i want to show, but that goal is achieved! What i don’t know how to do is have that column ( usedInRelation ) serchable and filterable. The commands in the top of the column are there but, not being the usedInRelation attribute a field in the db, the search gives an '‘unknown colum’ error if I put this row in the model’s search


$criteria->compare('usedInRelation',$this->usedInRelation,true);

Try the virtual attribute as a public property:




  public $usedInRelation = null;


  public function init()

  {


     $this->usedInRelation = $this->relationName ? 1 : 0;

  }    






instead of private $_usedInRelation with Setter and Getter.

Add ‘usedInRelation’ to the rules() as ‘safe’ on search.

In the rules it has been added.

The public function can’t work… it will always search for a column in the db with the attribute name.

The matter, i think, is to add some code to the search function in the model

$criteria->compare(‘usedInRelation’,$this->usedInRelation,true); This will not work if U dont have ‘usedInRelation’ in DB table, I think. Becasue "CDbCriteria represents a query criteria, such as conditions, ordering by, limit/offset. "

http://www.yiiframework.com/doc/api/1.1/CDbCriteria

U will have to add new attribute:

say your relation is

‘someRelation’ => [self::HAS_MANY ‘SomeTable’ [‘myModel_id’ => ‘id’]]

public $usedInRelationSearch;

make it safe on search

alter ‘name’ in your grid to be ‘usedInRelationSearch’

in search method

$criteria->with = [‘someRelation’];

$criteria->together = true;

if(isset($this->usedInRelationSearch))

{

if($this->usedInRelationSearch == 0)

// you want just records whose id is not in SELECT myModel_id from SomeTable

   $criteria->addCondition('someRelation.myModel_id <> '.$this->id);


else if ($this->usedInRelationSearch == 1)

// you want just records whose id is in SELECT myModel_id from SomeTable

   $criteria->addCondition('someRelation.myModel_id = '.$this->id);

}

hope this can help

Thanks again Dragan!

Sadly it doesn’t work :(

2 problems:

  1. the value of $this->usedInRelationSearch is always null as echoed by

 echo Yii::trace(CVarDumper::dumpAsString($this->usedInRelationSearch),'vardump');



but in the browser debugger i can see the correct value in the querystring (i.e. myModel[usedInRelationSearch]:1)

  1. the line $criteria->with = [‘someRelation’] breaks the output: the page is blank!

‘userInRelationSearch’ must be public and made ‘safe on search’ in model class rules. That’s why it’s always null, because U are doing mass assign and if attribute is not safe it will not be assigned to model.

this ‘someRelation’ should be real name of relation in your model class, please check this.

1 Like

it’s already as you are writing! double checked!

Hi

I am not sure what you are trying to achieve exactly, but I think that my extension http://www.yiiframework.com/extension/relatedsearchbehavior/ can help you set up adding a field from a relation in a GridView and make it searcheable.

thank you but it’s not for me (not now at least!)

Actually, Yii , because I think that the way you are going is actually more complex and the extension is a real timesaver.

Still, it is not clear what you want to achieve.

If you want only records that are involved in a relation, then you can try ‘together’, ‘with’, ‘joinType’=>‘INNER JOIN’.

If you want to test records in the relation against a search value then you "need" RelatedSearchBehavior.

Below you can see the could that would need to be added to a standard Model:


class MyModel extends CActiveRecord {

	public function rules()

	{

		return array

			/* ...*/

			array('relatedName,usedInRelation', 'safe', 'on'=>'search'),

		);

	}


	public function relations()

	{

		return array(

	        	'track'=>array(self::HAS_ONE,'Track',array('TrackId'=>'TrackId')),

		);

	}

	

	public $usedInRelation;

	public function init() {

       $this->usedInRelation = $this->relationName ? 1 : 0;

	}

	

	public function behaviors() {

    	return array(

            	// Add RelatedSearchBehavior

            	'relatedsearch'=>array(

                    	'class'=>'RelatedSearchBehavior',

                    	'relations'=>array(

                            	'relatedName'=>'track.Name',

								'relatedSearch'=>array(

									'field'=>'track.used',

									'searchvalue'=>'usedInRelation',

						),

					),

		    	);

	}

	

	public function search()

	{

		/** Other compares ... */

		

		/** Virtual attributes in the relations are searched automatically, no need to add them here. */

		

    	return $this->relatedSearch($criteria);

	}

	

	/**

 	* Not required, but to add autoscopes for attributes (uses RelatedSearchBehavior).

 	*/

    public function __call($name,$parameters) {

        try {

            return parent::__call($name,$parameters);

        } catch (CException $e) {

            if(preg_match('/'.Yii::t('yii',quotemeta(Yii::t('yii','{class} and its behaviors do not have a method or closure named "{name}".')),array('{class}'=>'.*','{name}'=>'.*')).'/',$e->getMessage())) {

                return $this->autoScope($name, $parameters);

            } else {

                throw $e;

            }

        }

    }

}



I want ‘only’ :rolleyes: to show if a record is involved in a relation in a column (true/false or 0/1 values) and this is already done.

Now i have to search/filter that column: show only records involved / show only records not involved

And that relation is what kind of relation on the root table: had_one, has_many, belongs_to, …

Then I think the question will be clear.

the relation is ‘has_many’

Some step has been done!

From Dragan post of 11:30 AM

this


$criteria->with = ['someRelation'];

causes no output in the page. Has been replaced with


$criteria->with = array(

                        'someRelation' => array('joinType'=>'LEFT JOIN')

                    );

The problem i didn’t get a value in trace is because the grid is update by ajax, so no new output but the value of $this->usedInRelationSearch is correct… i can see from the error I still get in the query;

the last problem is now that no value is passed for $this->id in


$criteria->addCondition('someRelation.myModel_id = '.$this->id);

I tryied to put it safe for search and input with no luck!

Only this little step and it will go!

Ok, I did it!

Thanks everybody for hints and suggestions

This is the working code in model search function





if(isset($this->usedInRelationSearch))

                {


                    if($this->usedInRelationSearch === '0'){

                        

                        $criteria->with = array(

                            'someRelation' => array('joinType'=>'LEFT JOIN')

                        );

                        $criteria->addCondition("someRelation.related_model_id IS NULL");

                        $criteria->together = true;  

                    }

                      

                    else if ($this->usedInRelationSearch === '1'){

                        $criteria->with = array(

                            'someRelation' => array('joinType'=>'LEFT JOIN')

                        );

                        $criteria->addCondition("someRelation.related_model_id IS NOT NULL");

                        $criteria->group = "t.model_id";

                        $criteria->together = true;  

                    }   

                }




Nice!

I am not sure that Yii uses the limit on relations, but you could add a "LIMIT 1" in your relation for this search to avoid bringing back all the records.

Thanks, i added


$criteria->limit = 1;