Matching Multiple Values In Cdbcriteria

Yii 1.1 General Discussion
1

So we have a text entry for our model where the user enters a city.


$model->city

We then have a search.

We want the user to be able to search for multiple cities

(e.g. comma separated list).

This code works fine for one city:


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

So then I went and tried this for the multiple match:


if ($this->city) {

   if (strpos($this->city,',') !==false) {

	$cities = explode(',',$this->city);		  

	foreach($cities as $c) {

		$criteria->compare('city',$c,true);

                //I also tried things like $criteria->compare('city',$c,true,"AND",true);

	} 

   } else {

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

   }

		}

But it doesn’t work - the search returns no elements as soon as we have a comma separated list of values.

Any ideas on how to resolve this?

Thank you

2

The DEMO Blog that comes with Yii does a TAG cloud using a text field and comma seperated values…I have been thinking about this type of problem myself. The tag table gets loaded as a single list of all values that are entered and used for type ahead logic.

I think this could be used to do searches if the array is maintained back to the parent row.

As for searching, could you work in something with wildcard or sets?

3

The DEMO Blog that comes with Yii does a TAG cloud using a text field and comma seperated values…I have been thinking about this type of problem myself. The tag table gets loaded as a single list of all values that are entered and used for type ahead logic.

I think this could be used to do searches if the array is maintained back to the parent row.

As for searching, could you work in something with wildcard or sets?

4

Try “OR” for the 4th parameter. :)

5

Look at addInCondition

6

Thanks all for the many responses!

I did try that and it doesn’t work.

Result is all models are returned (no filtering at all)

7

Great man, thanks,

that’s the fix!!!

Here is the final solution:


if ($this->city) {

		  if (strpos($this->city,',') !==false) {

		    $cities = explode(',',$this->city);

		      $criteria->addInCondition('city',$cities);

		  } else {

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

		  }

		}
8

Don’t you have another column to search for at the same time?

Then you have to use CDbCriteria::mergeWith.




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

if ($this->city) {

   if (strpos($this->city,',') !==false) {

        $criteria2 = new CDbCriteria();

        $cities = explode(',',$this->city);               

        foreach($cities as $c) {

                $criteria2->compare('city',$c,true, "OR");

        } 

        $criteria->mergeWith($criteria2);

   } else {

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

   }

}
9

addInCondition searches the strict matches, while you are using compare in partial match searching mode … so your solution works differently depending on the number of search words.

10

Hello,

I’m very happy to find this thread, and tried all the search methods to make it work with my case, and finally the last try ??? , the method from the topic starter solved it for me:

I have a field in which I have parameters (mostly numbers, some with a letter index) separated by commas and from my search field I wanted to be able to filter by writing there parameters separated by commas - to match even if it’s not in the same order as it is inserted into the db field. Thanks to p2m that he left the comment in his code. ::)

Now if any of the parameters matches with one from the parameter field, it will list it.

I would like to ask for a hint on how to transform this (as the other methods didn’t returned me anything for my searches) to a version that displays matches only if all the searched parameters are found in the item’s param field. In a parameter field there could be tens of comma separated values, but I would want to search after 3-4 paramaters only and get only those as matches which have all the 3-4 parameters included that I provided in my search string.

Thanks a lot!

11

As solution by softark, I have done same and it’s works like charm. thanks softark.

if you are working with multiple columns then do like this.

Views

  1. In _serach.php file,

    Create Textbox like this.




        <table class="event_search">

        <tr>

           <th>Event Id</th>

           <th>Event Name</th>

           <th>User First Name</th>

           <th>Comapany Name</th>

           <th>Task Name</th>

           <th>Notes </th>

            <th><a href="javascript:void(0)" style="float: right;font-weight: bold;text-decoration: none;font-size:19px;" onclick="addRow()">+</a></th>

       </tr>

       <tr class="main_row">

           <td>

               <input type="text" name="Event[event_id][]" placeholder=" Event Id ">

            </td>

           <td>

               <input type="text" name="Event[name][]" placeholder=" Event Name ">

           </td>

           <td>

               <input type="text" name="Event[user_first_name][]" placeholder=" User ">

           </td>

           <td>

               <input type="text" name="Event[company_name][]" placeholder=" Company ">

           </td>

           <td>

               <input type="text" name="Event[task_name][]" placeholder=" Task ">

           </td>

           <td>

               <input type="text" name="Event[notes]" placeholder=" Notes ">

           </td>




        </tr>


    </table>
  1. Add This script

   <script>

    $(function(){

        addRow = function(){


            var search_row = $("table.event_search tr:eq(1)").html();

            $(".event_search").append("<tr>"+search_row+'<td><a href="javascript:void(0)" style="float: right;font-weight: bold;text-decoration: none;font-size:15px;color:red;" class="remove">x</a></td>'+"</tr>");

            //alert(search_row);

        }

        $(".remove").live('click',function(){

           $(this).closest('tr').remove();

        });


    });

</script>
  1. Controller :: ############

public function actionIndex()

    {


        $model=new Event('search');

        $model->unsetAttributes();  // clear any default values

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




           foreach($_GET['Event'] as $key=>$value){


               if(is_array($value))

                foreach($value as $key1=>$val1){

                    if(empty($val1))

                    unset($_GET['Event'][$key][$key1]);

                }

           }


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

        }


        $this->render('index',array('dataProvider'=>$model->search(), 'model'=>$model)); //send model object for search

    }
  1. Model ###########

 

        $criteria2 = new CDbCriteria();

        if(is_array($this->company_name)){

            foreach($this->company_name as $val) {

                $criteria2->compare('company.name',$val,true, "OR");

            }

            $criteria->mergeWith($criteria2);

        }

        if(is_array($this->user_first_name)){

            foreach($this->user_first_name as $val) {

                $criteria2->compare('user.first_name',$val,true, "OR");

            }

            $criteria->mergeWith($criteria2);

        }
12

I have a similar problem like yours, but I cant figure out how to resolve:

I have a Product model, an Attribute model. In the Product model I have a MANY_MANY relation named attributes which loads all the attributes related to a product.

And I want to get only products which has a list of attributes.

For example: A smartphone with attributes: Android Operating System, 5mp Camera.

But I the search method I can only chack for one attribute.

Any suggestions?