Cdbcriteria Searching Separeted Values

Yii 1.1 General Discussion
1

Hello,

I have been trying to fix this problem reading a lot of post , searching on internet but i couldn’t.

I have the delimitedcolumn in the db with values like 123x456x789 and I would like to filter this values using the >, = and < operators.

I created a dummy page to get the strings splited:




$delimitedvalues = '123x456x789';

$first = substr($delimitedvalues,0,strpos($delimitedvalues,'x'));

$second = substr(substr($delimitedvalues,strpos($delimitedvalues,'x')+1),0,strpos(substr($delimitedvalues,strpos($delimitedvalues,'x')+1),'x'));

$third = substr(substr($delimitedvalues,strpos($delimitedvalues,'x')+1),strpos(substr($delimitedvalues,strpos($delimitedvalues,'x')+1),'x')+1);


echo $first;

echo '</br>';

echo $second;

echo '</br>';

echo $third;

but now I want to get all values that in the first part are greater than 123, then the second and third part but using the CDBCriteria.

I tried with this 3 examples but it didnt work.





$criteria->compare('substr(t.delimitedcolumn,0,strpos(t.delimitedcolumn,"x"))',$this->pieces,true);


//$criteria->select = '*,substr(t.delimitedcolumn,0,strpos(t.delimitedcolumn,"x"))';


//$criteria->addCondition('substr(t.delimitedcolumn,0,strpos(t.delimitedcolumn,"x"))');

also I tried to use the self::STAT in the model as following but the alias column was not found:




'delimitedcolumn_alias'=>array(self::STAT, 'Piece', 'id_piece',

			'select' => 'substr(t.delimitedcolumn,0,strpos(t.delimitedcolumn,"x"))'

Any ideas ? any help it will appreciate it.

Thanks.

2

Anyone ? Moderator ? I am lost here :D

Thanks

3

Take a look at CDbExpression.

4

Databases are not meant for putting delimited data into one column. You have to jump through hoops to get the substring and compare it, and it’s going to be slow. Just explode it into three columns when inserting, then you can easily do WHERE first > 123 AND second < 456.


list($first, $second, $third) = explode('x', '123x456x789');
5

Tsunami is right on point. Once you explode the col into three vars, make sure you have the associated properties defined in your class. Now you can define your comparison in the search method.

Assuming you defined the vars: $first, $second, $third.




class MyClass

{

    public $first;

    public $second;

    public $third;


    ....


    public function search()

    {

     	....

     	$criteria->compare('first' , '>='.$this->first);

     	$criteria->compare('second', '>='.$this->second);

     	$criteria->compare('third', '>='.$this->third);

     	....

    }

}
6

Awesome ! You are the man !

I have used the following code:





		$first=new CDbExpression('convert(substring(t.delimitedcolumn,1,locate("x",t.delimitedcolumn)-1),signed)');

		$criteria->compare($first,$pieces_c[0],true);

		$criteria->compare($first,$pieces_c[1],true);

Thank you very much for your help ORey ! :D