Yii Framework Forum: Model Search() Filter By Relation - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Model Search() Filter By Relation Rate Topic: -----

#1 User is offline   Phyxius 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 12-November 12

Posted 04 January 2013 - 06:14 PM

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
0

#2 User is offline   Backslider 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 363
  • Joined: 23-July 09

Posted 04 January 2013 - 07:07 PM

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.
We were all once expert at....... nothing.

yii-language-behavior

My Blog
0

#3 User is offline   Phyxius 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 12-November 12

Posted 05 January 2013 - 11:25 AM

View PostBackslider, on 04 January 2013 - 07:07 PM, said:

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 !
0

#4 User is offline   Phyxius 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 12-November 12

Posted 05 January 2013 - 02:46 PM

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.

Quote

Error 500: <h1>CDbException</h1>
<p>CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':ycp0) AND (apellido_paterno LIKE :ycp1)) AND (proyecto.year = '2012')) LIMIT ' at line 1


please help me =/
0

#5 User is offline   Backslider 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 363
  • Joined: 23-July 09

Posted 05 January 2013 - 02:56 PM

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

Need to see the whole query.
We were all once expert at....... nothing.

yii-language-behavior

My Blog
0

#6 User is offline   Phyxius 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 12-November 12

Posted 05 January 2013 - 07:21 PM

This is the error.

Quote

Error 500: <h1>CDbException</h1>
<p>CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':ycp0) AND (proyecto.añoFS = '2012')) LIMIT 20' at line 1. The SQL statement executed was: SELECT `t`.`_id` AS `t0_c0`, `t`.`run` AS `t0_c1`, `t`.`rol_usm` AS `t0_c2`, `t`.`nombre` AS `t0_c3`, `t`.`apellido_paterno` AS `t0_c4`, `t`.`apellido_materno` AS `t0_c5`, `t`.`proyecto_id` AS `t0_c6`, `t`.`jefe_grupo` AS `t0_c7`, `t`.`sede` AS `t0_c8`, `t`.`profesor_id` AS `t0_c9`, `t`.`carrera` AS `t0_c10`, `t`.`correo` AS `t0_c11`, `proyecto`.`_id` AS `t1_c0`, `proyecto`.`pre_empresa` AS `t1_c1`, `proyecto`.`proyecto` AS `t1_c2`, `proyecto`.`añoFS` AS `t1_c3` FROM `alumno` `t` LEFT OUTER JOIN `proyectos` `proyecto` ON (`t`.`proyecto_id`=`proyecto`.`_id`) WHERE ((proyecto_id=:ycp0) AND (proyecto.añoFS = '2012')) LIMIT 20

This post has been edited by Phyxius: 06 January 2013 - 10:03 PM

0

#7 User is offline   Phyxius 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 12-November 12

Posted 06 January 2013 - 10:07 PM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users