Form fields not related to a model not added to CDbCriteria?

Yii 1.1 General Discussion
1

Hi there,

I have a code like this:


$criteria = new CDbCriteria;

$criteria->compare('BCODE', $model->BCODE, true);

$criteria->compare('PATIENT', mb_strtoupper($model->PATIENT, 'UTF-8'), true);

$criteria->compare('PESEL', $model->PESEL, true);

$criteria->select = 'ID, STAT, DATR, BCODE, PATIENT, PESEL, UDATE';

$criteria->condition .= " AND (TO_DATE(DATR, 'YYYY-MM-DD HH24:MI:SS') < Nvl('".$model->registrationDateStart."', sysdate))";

where $model->registrationDateStart is the only property of my model not related to any of columns in table.

This CDbCriteria is then put to CActiveDataProvider and it is sent to update CGridView.

When I drop SQL generated by AJAX request to update CGridView I see something like this:


Querying SQL: SELECT COUNT(*) FROM WWW_ZLEC t WHERE ((BCODE LIKE :ycp0) AND (PATIENT NOT LIKE :ycp1)) AND (PESEL LIKE :ycp2) AND (TO_DATE(DATR, 'YYYY-MM-DD HH24:MI:SS') < Nvl('', sysdate)). Bind with parameter :ycp0='%800%', :ycp1='%KOWALSKI%', :ycp2='%17%'

As you can see, the condition I add in last line of my CDbCriteria is returing an empty string instead what user entered to related form field. While SQL parts that comes from other form fileds (related to Model properties) are properly filled with values (by binding).

Why this happens like this? Is this because Yii uses parameters (values) binding and I’m trying to insert variable value directly to a string? Or did I made mistake somewhere else?

EDIT: And, if this is the reason, how to bind a value, if this is done in CDbCommand, not in CDbCriteria?

2

In your example … when you use condition… you are directly assigning the value of $model->registrationDateStart… you are not binding the value…

It’s all OK… you just need to check if there is some value to use… like




if(!empty($model->registrationDateStart)) // or any check you need

   $criteria->condition .= " AND (TO_DATE(DATR, 'YYYY-MM-DD HH24:MI:SS') < Nvl('".$model->registrationDateStart."', sysdate))";
3

Thank you. The problem is that this value is empty and I think I know why. So my next question is just to assure myself.

My controller is generating a view where I want to use that additional model property like that:


public function actionIndex()

{

    	$this->layout = '//layouts/column1';


    	$model = new Zlecenia('search');


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

    	if(isset($_GET['Zlecenia'])) $model->attributes = $_GET['Zlecenia'];


    	$this->render('index', array('model'=>$model));

}

Does it means that line:


if(isset($_GET['Zlecenia'])) $model->attributes = $_GET['Zlecenia'];

is only filling these model attributes that are related to any table column? I.e. I declared that additional property registrationDateStart. Value taken from corresponding form field comes along with other in one common array (Zlecenia in this example). Do I have to take care manually to send value from that additional model property to a view, or $model->attributes = $_GET[‘Zlecenia’]; should do this?

It seems that either this or something else is causing that this property is send empty to a view, even if user fills corresponding form field.

4

No…

it fills only "safe" attributes… did you set your attribute as "safe" (for proper scenario)

5

Boy, I’m moron! :]

I was thinking about doing it this way (controller):


public function actionIndex()

{

    	$this->layout = '//layouts/column1';


    	$model = new Zlecenia('search');


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

    	if(isset($_GET['Zlecenia'])) $model->attributes = $_GET['Zlecenia'];

    	$regDateStart = (isset($_GET['Zlecenia']['registrationDateStart'])) ? $_GET['Zlecenia']['registrationDateStart'] : '';


    	$this->render('index', array

    	(

            	'model'=>$model,

            	'regDateStart'=>$regDateStart,

    	));

}

But of course, your solution is much more prefect and easier! :] Thank you!

6

All right, one more question. I tried to change code like this:


if($model->registrationDateStart != '') $criteria->condition .= " AND (TO_DATE(DATR, 'YYYY-MM-DD HH24:MI:SS') > Nvl('".$model->registrationDateStart."', sysdate))";

if($model->registrationDateEnd != '') $criteria->condition .= " AND (TO_DATE(DATR, 'YYYY-MM-DD HH24:MI:SS') < Nvl('".$model->registrationDateEnd."', sysdate))";

with a code like this:


if($model->registrationDateStart != '') $criteria->compare("TO_DATE(DATR, ''YYYY-MM-DD HH24:MI:SS'')", "> NVL(''".$model->registrationDateStart."'', SYSDATE))");

if($model->registrationDateEnd != '') $criteria->compare("TO_DATE(DATR, ''YYYY-MM-DD HH24:MI:SS'')", "< NVL(''".$model->registrationDateEnd."'', SYSDATE))");

which is more convenient for me.

But this does not work, because CDbCriteria.compare (or something else - probably PDO binding params) is escaping single quote characters with slashes in parameters being bind and in the result, query sent to DB is:


Querying SQL: SELECT COUNT(*) FROM WWW_ZLEC t WHERE (TO_DATE(DATR, ''YYYY-MM-DD HH24:MI:SS'')>:ycp0) AND (TO_DATE(DATR, ''YYYY-MM-DD HH24:MI:SS'')<:ycp1). Bind with parameter :ycp0=' NVL(\'\'2010-06-10\'\', SYSDATE))', :ycp1=' NVL(\'\'2010-07-21\'\', SYSDATE))'

which is incorrect (at least in Oracle).

7

You can use addCondition()

8

Thank you! :] Work like a charm!