blank fields in forms insert replace default null columns with empty string
#1
Posted 05 November 2009 - 02:04 PM
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
#3
Posted 23 December 2009 - 03:37 PM
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?
#4
Posted 30 May 2010 - 05:19 AM
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?
#5
Posted 30 May 2010 - 06:05 AM
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'); ); }
#6
Posted 30 May 2010 - 07:39 PM
Mike, on 30 May 2010 - 06:05 AM, said:
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.
#7
Posted 07 June 2010 - 09:06 AM
public function beforeSave() { foreach ($this->attributes as $key => $value) if (!$value) $this->$key = NULL; return parent::beforeSave(); }
#9
Posted 27 September 2010 - 09:40 AM
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.
#10
Posted 03 December 2010 - 12:29 AM
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"
#11
Posted 03 December 2010 - 07:07 AM
Saleem Kadiwal, on 03 December 2010 - 12:29 AM, said:
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
#12
Posted 04 August 2012 - 06:34 AM
Mike, on 30 May 2010 - 06:05 AM, said:
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.*' ),
#13
Posted 04 August 2012 - 06:53 AM
public function rules() { return array( … array('someAttribute, someOtherAttribute', 'default', 'setOnEmpty' => true, 'value' => null), …
#14
Posted 04 August 2012 - 06:56 AM

http://www.yiiframew...tOnEmpty-detail
#15
Posted 04 August 2012 - 07:54 AM
bennouna, on 04 August 2012 - 06:53 AM, said:
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.