[cshehadi]

Hi,

When I insert a blank form into a table that has columns defined as default null, Yii seems to replace those null values with an empty string. This can create problems with my data as I'll often search for IS NULL or NOT NULL, and empty string will appear as NOT NULL in those queries.

I've gotten around this by setting a "default" validator for the fields in question to null, but I'm wondering if there's a way to get Yii to do this automatically.

In other words - should Yii's default behavior be - when inserting or updating a blank text field into a column that contains NULL and also has a default value of NULL - keep it null, don't replace it with an empty string?

Hoping to start a discussion of the pros and cons of doing this, and whether there's a way to have it happen automatically in Yii.

-Charlie

[gallego123]

I'm interested in an answer to this

[PRWilly]

Even if there was something in the Model that would understand or take a list of acceptable NULLable values.
If Date is set to '0000-00-00' or '' I'd want them to be NULL in the database. While someone else may WANT to store the '0000-00-00' but not the empty string as NULL.

Anyone?

[Tova]

If someone still needs - found solution im phundament framework von schmunk
array('checkAccess', 'default', 'setOnEmpty' => true, 'value' => null),

This is question for developers - why Yii is inserting empty values instead of null's, even if databases column default value is set to be NULL?

[Mike]

Yii doesn't know about any default value in DB and even if a string is empty: It's a valid string which is !==null. If all empty strings would get converted to null, how could you ever save an empty string to DB? So i'm against letting AR do this automatically. Instead, these columns should be specified manually with a rule like the one schmunk used.

My approach would have been to create a little helper:

function empty2null($value) {
   return $value==='' ? null : $value;
}

This way i can change, what is considered "empty". This helper can then be used in a filter rule:

public function rules() {
    return array(
        array('somecolumn,othercolumn', 'filter', 'filter'=>'empty2null');
    );
}

[Tova]

Mike, on 30 May 2010 - 06:05 AM, said:

Yii doesn't know about any default value in DB and even if a string is empty: It's a valid string which is !==null. If all empty strings would get converted to null, how could you ever save an empty string to DB?

Same way as now we inserting nulls

array('checkAccess', 'default', 'setOnEmpty' => true, 'value' => ''),

But I think is too late to change this in 1.x. Ok, thank you for explanation.

[youngestlinton]

I wanted to add NULL to any fields in multiple models and didn't really want to add multiple rules. So...

public function beforeSave() {
	foreach ($this->attributes as $key => $value)
		if (!$value)
			$this->$key = NULL;
		
	return parent::beforeSave();
}

[H Samuels]

thanks! this was very helpful to me.

[RedRabbit]

I came across this unexpected behaviour too.
I agree that for a string value, yii cannot know whether it should write an empty string or NULL.
However, yii does know (or guesses quite well) the format of each field, and whether it is required or not. Therefore, in the case of an integer field for which NULL is allowed, it seems to me that NULL is the only possible default value, since an empty string is obviously not going to work in an integer field, and giving the value 0 (or some such value) is just arbitrarily guessing what was intended.
I'm going to go with the filter solution for now - adding a filter to all my integer values that can also accept NULL (usually foreign keys in my case), but I do think that this is something that could be handled automatically by yii.

[Saleem Kadiwal]

youngestlinton has already provided the solution for the problem, but for your case you can make it a part of yii framework by adding

		
foreach ($this->attributes as $key => $value)
       	if (!$value)
            	$this->$key = NULL;

in beforeSave() function at the start

The function can be found in "Yii/framework/db/ar/CActiveRecord.php"

[Mike]

Saleem Kadiwal, on 03 December 2010 - 12:29 AM, said:

in beforeSave() function at the start

The function can be found in "Yii/framework/db/ar/CActiveRecord.php"

Hi and welcome.

I'm sorry, but your proposal is a bad idea. And there are 2 good reasons, why:

1. You should not change any framework files. You'd have to apply these changes whenever you want to upgrade to a new Yii version and you might introduce incompatibilities with other code (extensions, etc.)

2. Your solution would convert any value that is considered false (0, empty string, "0", ...) to NULL. So it ignores those DB fields that could e.g. contain an empty string ('') or 0 as value. All those would also be converted to NULL.

Instead you could use this extension:
http://www.yiiframew...ynullvalidator/

And some upcoming release might probably contain a generic fix:
http://code.google.c.../detail?id=1750

[alecgregory]

Mike, on 30 May 2010 - 06:05 AM, said:

My approach would have been to create a little helper:

function empty2null($value) {
   return $value==='' ? null : $value;
}

This way i can change, what is considered "empty". This helper can then be used in a filter rule:

public function rules() {
    return array(
        array('somecolumn,othercolumn', 'filter', 'filter'=>'empty2null');
    );
}

This is a great helper function. So useful in fact, that I put it in its own class so I can access it from various models. It took me a while to work out how to do this and I'm not sure I've done it the best way, so I'd be keen to hear any feedback:

The Class, EmptyToNull, which I placed in protected/filters/EmptyToNull.php

class EmptyToNull extends CFilterValidator
{
    public function emptyToNull($value)
    {
        // logic being applied before the action is executed
        return $value==='' ? null : $value;
    }
}

Calling the emptyToNull method in the above class from a model class was the tricky bit

array('impliedOrder, explicitOrder', 'filter', 'filter'=>array(new EmptyToNull($this), 'emptyToNull'))

To ensure the EmptyToNull class is available to instantiate, I added application.filters.* to the import array

// autoloading model and component classes
	'import'=>array(
		'application.models.*',
		'application.components.*',
                'application.filters.*'
	),

[bennouna]

@alecgregory, you can use the setOnEmpty property like the following in standard (normally added automatically by Gii if it finds a db field that allows NULL value):

    public function rules() {
        return array(
            …
            array('someAttribute, someOtherAttribute', 'default', 'setOnEmpty' => true, 'value' => null),
            …

[bennouna]

Oh ok I see it's a Yii 1.0.x discussion. It seems it was there already

http://www.yiiframew...tOnEmpty-detail

[alecgregory]

bennouna, on 04 August 2012 - 06:53 AM, said:

@alecgregory, you can use the setOnEmpty property like the following in standard (normally added automatically by Gii if it finds a db field that allows NULL value):

    public function rules() {
        return array(
            …
            array('someAttribute, someOtherAttribute', 'default', 'setOnEmpty' => true, 'value' => null),
            …

Ah splendid, thanks. It was useful finding out now to call external validators in general, I guess.