Adding Between And Null Condition To Compare Method Of Cdbcriteria

Yii 1.1 General Discussion
1

I just wanted to add between and null condition to filter data in all gridviews across entire app and as it was a simple alteration of compare method of CDbCriteria I enjoy to share it with others




class RmDbCriteria extends CDbCriteria

{

    public function compare($column, $value, $partialMatch=false, $operator='AND', $escape=true, $nullSign='null', $betweenSign='-')

	{

		if(is_array($value))

		{

			if($value===array())

				return $this;

			return $this->addInCondition($column,$value,$operator);

		}

		else

			$value="$value";


		if(preg_match('/^(?:\s*([\w\d\s]*)(<>|<=|>=|<|>|=|'.$betweenSign.'))?(.*)$/',$value,$matches))

		{

			$value1=$matches[1];

                        $value=$matches[3];            

			$op=$matches[2];

		}

		else

			$op='';


		if($value==='')

			return $this;


		if($partialMatch)

		{

                       if($value==$nullSign)

                        {

                            $this->addCondition($column.' is null');

                           return $this;

                        }

			if($op==='')

				return $this->addSearchCondition($column,$value,$escape,$operator);

			if($op==='<>')

				return $this->addSearchCondition($column,$value,$escape,$operator,'NOT LIKE');

                        

		}

		else if($op==='')

			$op='=';

        

        if($op==$betweenSign)

        {

            $this->addBetweenCondition($column,$value1,$value); 

            return $this;

        }

        

        

        if($value==$nullSign && $nullSign!='0')

        {

            $this->addCondition($column.' is null');

            return $this;

        }

        

        if($nullSign=='0')

        {

                $column='IFNULL('.$column.',0)';

        }

            

		$this->addCondition($column.$op.self::PARAM_PREFIX.self::$paramCount,$operator);

		$this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;


		return $this;

	}

    

}

with this in place you can filter between two values in gridview by "-" (or your desired charachter)

eg: 5-10 means >=5 and <=10

you can filter null values as well

and if you set nullSign as zero it treats null as zero

2

Dear Reza m

Excellant stuff. Keep doing it.

In the previous thread related to displaying null values, you have given a nice solution.

I think you have extended it further in this post.

I was trying to equate null values to ‘0’.

For example, if some one searches like <1 or <10, it should both include 0 and null values as well.

I miserably failed in that attempt.

Have you got any idea in solving this.

regards.

3

I edited it so now it does the job if you set $nullSign as zero

eg:




$criteria->compare('my_column',$this->my_column,false,'AND',true,'0');
4

Dear Reza M

Many many thanx.

I tested the code in my localhost. I am facing following problems.

1.the operator "<" with value 0 brings null values.

2.the opertor ">=" is not bringing null values.

3.Once selected null values persists. I could not eliminate them by filtering actions in other columns.

4.’>-10’ ,is not bringing the null values.

  1. "<-10" is bringing the null values.

Anyway It is great work.

I hope you will come up with perfect solution!

Meanwhile I findout a workaround for this problem with native CDbCriteria.

I have a column weight with lot of missing values after joining two tables.(patient,anthropometry)

Following is the code in my search method in model.




public function search()

	{

	


		$criteria=new CDbCriteria;

		$criteria->select='*, IFNULL(weight,0) AS weight ';

                $criteria->join="LEFT JOIN anthropometry ON t.id=anthropometry.p_id";

                $criteria->together=true;

		$criteria->compare('t.id',$this->id);

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

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

		

		$criteria->compare('IFNULL(weight,0)',$this->weight);

	        

                return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'sort'=>array('attributes'=>array('*','weight'=>array(

			    'asc'=>'IFNULL(weight,0) ASC',

			    'desc'=>'IFNULL(weight,0) DESC',

			   )),

		)));

		

	}

Now all the undefined fields are filled with "0".

They exactly behave as "0" in filtering and as well as sorting.

Many Regards.

5

Your solution is complete and thanks to you, I changed this part of code and I think it works now




if($nullSign=='0')

{

     $column='IFNULL('.$column.',0)';

}