searching in two columns

net16 · September 8, 2012, 11:51pm

Hello

I have a table with few columns. I have displayed all needed columns and now I have problem with searching.

I would like to display all records which contain some name inside at least one from two columns (second OR third).

For example, we have table:

1 red blue

2 red green

3 blue orange

4 green red

5 blue green

We want to display records wich contain red inside second or third column. Output should be:

1 red blue

2 red green

4 green red

In model I have created function searchnew(). I do not know how criterias should be look.

Could you help me? I would be very appreciate.

softark · September 9, 2012, 8:27am

Hi net16,

First, introduce a virtual attribute for the search parameter, and declare it to be ‘safe’ on ‘search’ scenario.




class MyModel extends CActiveRecord {

    public $color_search;

    ...

    public function rules() {

        return array(

          ...

          array('xxx, yyy, color_search', 'safe', 'on'=>'search'),

        );

    }

    ...

    public function attributeLabels()

    {

        return array(

            'xxx' => 'XXX',

            'yyy' => 'YYY',

            'color_search' => 'Color',

            ...

        );

    }

    ...

}

This ‘color_search’ is the holder of the color name that will be searched.

Because you will use it in the search form, give it a label, too.

And then in your searchnew method, compare ‘color_search’ with the colors:




    $criteria = new CDbCriteria;

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

    $criteria->compare('third', $this->color_search, true, 'OR');

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

        'criteria' => $criteria,

    ));

The point here is the 4th parameter of compare().

And if you have to compare other attributes than the colors at the same time, then:




    $criteria = new CDbCriteria;

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

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

    if ($this->color_search != '') {

        $criteria2 = new CDbCriteria;

        $criteria2->compare('second', $this->color_search, true);

        $criteria2->compare('third', $this->color_search, true, 'OR');

        $criteria->mergeWith($criteria2);

    }

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

        'criteria' => $criteria,

    ));

In this case you have to use mergeWith().

Look up the class reference of CDbCriteria for compare() and mergeWith().

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

net16 · September 9, 2012, 11:06am

Thank you for your help. I have implemented your idea but my code still does not work. When I choose such fruit I get empty table (No results found) and any errors. All columns in the table are searched correctly. Only _printfruit_search does not work. Maybe I have another problem in the code?

printfruit.php:


<div class="search-form">

<?php $this->renderPartial('_printfruit_search',array(

	'model'=>$model,

)); 

?>

</div><!-- search-form -->

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

	'id'=>'fruit-grid',

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

	'filter'=>$model,

	'columns'=>array(

        ...

		array(

			'name'=>'chosenFruit',

			'type'=>'raw',

			'value'=>'$data->chosen->fruitName',

			'visible'=>true,

		),

		array(

			'name'=>'userFruit',

			'type'=>'raw',

			'value'=>'$data->user->fruitName',

			'visible'=>true,

		),

        ...

_printfruit_search.php:




<?php $form=$this->beginWidget('CActiveForm', array(

	'action'=>Yii::app()->createUrl($this->route),

	'method'=>'get',

)); ?>


	<div class="row">

		<?php echo $form->label($model,'printFruit'); ?>

		<?php echo $form->dropDownList($model,'printFruit', CHtml::listData(Fruit::model()->findAll(), 'id', 'fruitName'), array('empty'=>'Please select')); ?>

	</div>

	

	<div class="row buttons">

		<?php echo CHtml::submitButton('Search'); ?>

	</div>


<?php $this->endWidget(); ?>

and Fruit.php model:




public function searchprint()

	{


		$sort = new CSort();

		$sort->attributes = array(

...

			'userFruit'=>array(

			  'asc'=>'user.fruitName',

			  'desc'=>'user.fruitName desc',

			),

			'chosenFruit'=>array(

			  'asc'=>'chosen.fruitName',

			  'desc'=>'chosen.fruitName desc',

			),

		...

		);


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

		// should not be searched.


		$criteria=new CDbCriteria;

		

		$criteria->with=array('user', 'chosen');


		...

		$criteria->compare('user.fruitName',$this->userFruit,true);

		$criteria->compare('chosen.fruitName',$this->chosenFruit,true);

		

		if ($this->printFruit != '') {

        $criteria2 = new CDbCriteria;

        $criteria2->compare('user.fruitName', $this->printFruit, true);

        $criteria2->compare('chosen.fruitName', $this->printFruit, true, 'OR');

        $criteria->mergeWith($criteria2);

    }




		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'sort'=>$sort,

			'pagination'=>array(

				'pageSize'=>17,

			),

		));

		

		

	}

softark · September 9, 2012, 11:46am

Please check for errors in your ‘protected/runtime/application.log’. It may tell you something.

Some questions:

Did you add ‘printFruit’ to the rules for search?
What type is your ‘chosen’ relation? HAS_MANY or BELONGS_TO?

If it is HAS_MANY, try "together" option for a test.




$criteria->with = array('user', 'chosen');

$criteria->together = true;

net16 · September 9, 2012, 12:44pm

In logs I have no information connected with this problem.

Rules for search - yes, I have added




public function rules()

{

...

array('userFruit, chosenFruit, printFruit', 'safe', 'on'=>'search'),

}

relations:




public function relations()

	{

// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'chosen' => array(self::BELONGS_TO, 'Fruit', 'chosen_id'),

			'user' => array(self::BELONGS_TO, 'Fruit', 'user_id'),

		);

}

It is BELONGS_TO relation unfortunately. What can be wrong yet?

softark · September 9, 2012, 1:18pm

Ah, OK, I think I got it.

Your $model->printFruit is not a string (a name of a fruit), but a code (id of Fruit), because you are using a dropDownList for the input of it. Right?

So, … you know what to do.

net16 · September 9, 2012, 2:47pm

You are great!

I have modified following criterias:




$criteria2->compare('user.id', $this->printFruit, true);

$criteria2->compare('chosen.id', $this->printFruit, true, 'OR');

It works!!

Thank you for your patience and taking the time. Thank you for all!!

softark · September 9, 2012, 2:55pm

Um, the 3rd parameter ($partialMatch) should be false.




$criteria2->compare('user.id', $this->printFruit, false);

$criteria2->compare('chosen.id', $this->printFruit, false, 'OR');

net16 · September 9, 2012, 3:34pm

Yes, thank you again!

net16 · September 9, 2012, 4:32pm

I do not want to exceed decency, but maybe it will be simple answer.

For above searching I have prepared dropDownList in this needed manner:




<div class="row">

		<?php echo $form->label($model,'printFruit'); ?>

		<?php echo $form->dropDownList($model,'printFruit', CHtml::listData(Fruit::model()->findAll('email<>:name AND enabled=:status',

		array(

				':name'=>Yii::app()->params['fruitEmail'],

				':status'=>'1',

		)), 'id', 'fruitName'), 

		array(

		'empty'=>'Please select')); ?>

	</div>

I would like to receive order ASC on that list. I have tried on different manner, but all is wrong. I think that the problem is associate to first condition of findAll:




'email<>:name AND enabled=:status',

		array(

				':name'=>Yii::app()->params['fruitEmail'],

				':status'=>'1',

		)

How can I use


'order' => 'fruitName ASC',

on this list. It is not standard using.

Please help again if you have some time.

abennouna · September 9, 2012, 4:59pm

The findAll() doc says that you can use a string condition or a CDbCriteria array:


<?php echo $form->dropDownList($model, 'printFruit', CHtml::listData(

                    Fruit::model()->findAll(

                        array(

                            'condition' => 'email<>:name AND enabled=:status',

                            'params' => array(

                                ':name'=>Yii::app()->params['fruitEmail'],

                                ':status'=>'1',

                            ),

                            'order' => 'fruitName ASC'

                        )

                    ),

                    'id', 'fruitName'), 

                    array('empty'=>'Please select')

               ); ?>

net16 · September 9, 2012, 5:18pm

Thank you very much, it is working!