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.
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.
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');
);
}
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.
I’m sorry, but your proposal is a bad idea. And there are 2 good reasons, why:
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.)
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.
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
@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),
…
