Model Search() Filter By Relation

Hello!

I have a problem trying to filter my results in my controller.

I got 2 tables called Alumno and Proyecto, these tables are related by alumno.proyecto_id and proyecto._id as suposed to.

Most topics in the forum use the admin view from the blog example. But i can’t understand how to filter the results of $model->search() (as dataprovider to a Cgridview) to show only the “Alumno” whose “proyecto.year” equals some specific value.

Controller Action:




$model = new Alumno('search');


    $model->unsetAttributes();

    if (isset($_GET['Alumno'])) {

        $model->attributes = $_GET['Alumno'];

    }

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

        'model' => $model,

        'year'=>$year,

    ));



View:




$this->widget('zii.widgets.grid.CGridView', array(

    'id'=>'alumnos-grid',

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

    'filter'=>$model,              //from the controller action

    'columns'=>array(

            '_id',

            'nombre',

            'apellido_paterno',

     ),

));



Please help me to understand how to filter these data.

Greetings

Declare ‘year’ as a public property in your Alumno class.

In the model search you can use $criteria->with to join the relation (you must declare the relation in your model, eg. ‘proyectoRel’).

Something like this:




    if(!empty($this->year)) {

	$criteria->with = 'proyectoRel';

	$criteria->addCondition("proyectoRel.year = '" . $this->year . "'");

    }



Make sure that ‘year’ is suitably sanitized.

Thanks for your reply, it was very usefull.

this is my new search method:


public function search($year1=null)

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$this->year = $year1; 

		

		$criteria=new CDbCriteria;


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

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

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

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

		

		 if(!empty($this->year)) {

        $criteria->with = 'proyecto';

        $criteria->addCondition("proyecto.year= '" . $this->year . "'");

    }


return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}




And to filter the search I use:


$dataProvider = $model->search('2013');

in my model.

thanks you !

I now realize that this aproach made the Cgridview filters stop working.

Now when i try to search by any value I get a mysql syntax error.

please help me =/

It looks like there is an extra bracket here: AND (proyecto.year = ‘2012’)) <<<

Need to see the whole query.

This is the error.

It seems like there’s some problem in the search method.

When i do:


public function search($year1=null)

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.

		

		$criteria=new CDbCriteria;


		...

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

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

		...

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

		


		$this->year = $year1;     

		

		

		if(!empty($this->year))     //THIS IS ALWAYS TRUE

		{

	    	     $criteria->with = 'proyecto';

	    	     $criteria->addCondition("proyecto.añoFS = '" . $this->year . "'");

   		}

		

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

I get an error when i use filters in the cgridview , but the search filter by year works fine. If i don’t give the method a year the “if” is false and the filters work fine.

Please help me.