Advanced search-condition

Hello!

I have a table containing athletes(swimmers), the table includes swimmer_id, result_time, result_date.

In a CGridview, I want to be able to do a search that shows all the result_times that was made when a swimmer was at a certain age.

For example, If I search between ages 20-23 I want to show results from when the swimmers where at that age.

I have a relation that links swimmer_id with a swimmer-table that stores birthdates etc.

So I guess I need to add a condition that does something like this:

result_date - swimmer.date_of_birth = the between-ages that was typed in

Is this possible to accomplish?

surly you can . there may exist many ways , i think : Searching and sorting by related model in CGridView

or RAR may help you out ! :lol:

I have come up with something like this(with two public variables created($year_first and $year_last) and added to ‘safe’):

search() function in Result-model




if($this->year_first != '' && $this->year_last != '') {

             $criteria->addBetweenCondition('swimmer.date_of_birth', ''. $this->year_first.'', '' . $this->year_last .'');

}



But I need to calculate result_date - swimmer.date_of_birth before I make the comparison.

I want to do something like this but it doesn’t work




if($this->date_first != '' && $this->date_last != '') {

             $criteria->addBetweenCondition('result_date - swimmer.date_of_birth', ''. $this->date_first.'', '' . $this->date_last .'');

}



Any ideas on this?

Try this:

in your Cgridview for example:




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


	'id'=>'Your-grid',


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


	'filter'=>$model,


	'columns'=>array(


		array('name'=>'age','value'=>'round((strtotime($data->result_date)-strtotime($data->swimmer->date_of_birth))/(366*24*60*60))'),//this gives the age, modify attributes according to yours




In your model result, add these lines where required:-





	public $age; // to be used to store age


  




      public function rules()

	array('attrib1, attrib2, attrib3, age', 'safe', 'on'=>'search')







	public function search()


	{


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


		// should not be searched.





		$criteria=new CDbCriteria;

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

		$criteria->compare('substring(result_date,1,4)-substring(swimmer.date_of_birth,1,4)',$this->age);		

                .........

                ..........

		return new CActiveDataProvider(get_class($this), array(


			'criteria'=>$criteria,

    		'sort'=>array(

        		'attributes'=>array(

 			        'age'=>array(

						'asc'=>'(substring(result_date,1,4)-substring(swimmer.date_of_birth,1,4))',

						'desc'=>'(substring(result_date,1,4)-substring(swimmer.date_of_birth,4)) DESC'

					),


..........

...........

.....



I didn’t really understand that last part but I came up with my own solution:




if ($this->date_first != '')

		{

		$criteria->addCondition(

                'EXISTS(

                		

                       	SELECT *

						FROM Swimmer

						INNER JOIN Result ON Swimmer.swimmer_id = t.swimmer_id

						WHERE (YEAR(t.result_date) - YEAR(Swimmer.date_of_birth)) = :date

                )'

        );

		$criteria->params['date'] = $this->date_first;

		}