select helper with boolean and null/nil values

rubyonrails-talk
1

I have a boolean column that I would like to modify with a select helper element, and have :include_blank map to (database) null.

This *almost* works. On the create/edit/update page, if you select true/yes it sets the column to true, if you select No/false it sets it to false, and if you set it to blank, it sets it to (database) null. fantastic.

Now you edit the record. If it is "True" in the database the select box picks the correct value. If it was set to 'no' or (database)null however the selection is set to the blank value.

This is especially problematic if you edit any other field on the form, as it resets *everything* back to 'null', regardless of if it was "false" or "null" to beginwith.

I have constructed a trivial test application to demonstrate this:

http://www.cs.rpi.edu/~crossd/selecttest.tar.bz2

Fire it up in 'rails server' and go to http://127.0.0.1:3000/testings

(I've included a sqlite3 db there with 2 rows in it). edit one, set it to 'Yes', check the 'show' page, edit it again, set it to 'No', check the 'show' page, edit it again, set it to blank (you won't have to, its already there), and save it and check the show page. notice all 3 edits work correctly, but the initial value is NOT set correctly for for the 'False' case.

Is this a bug? am I doing something wrong?

2

I believe your problems are coming from the fact that you allow something different than true/false in your DB column. If the column is supposed to contain a boolean value, why are you allowing it to contain the null value? A boolean value is either true or false, not null. If I were you I would force the value to be one or the other and make the column not null capable. You can enforce the rule with one of the validation helpers. This is extracted from the 'validates_presence_of' documentation:

"If you want to validate the presence of a boolean field (where the real values are true and false), you will want to use validates_inclusion_of :field_name, :in => [true, false]. "

If you don't want to display errors and just control the issue silently you can always use before_save to change the value to false if needed. Something like:

<code> def before_save   self.my_boolean_column = false unless self.my_boolean_column == true end </code>

You should check against the value "true" (meaning that you shouldn't leave the equality comparison out) because otherwise you could run into problems if the value is ever something other than true/false/ null. A simple empty string would return "true" on the condition otherwise as this example shows:

# This works in all cases

a = true

=> true

puts 'true' if a == true

true => nil

puts 'true' if a

true => nil

# This works only when comparing against 'true'

a = ''

=> ""

puts 'true' if a == true

=> nil

puts 'true' if a

true # Notice how this would make the code above work incorrectly on the before_save => nil

3

pepe wrote in post #962574:

I believe your problems are coming from the fact that you allow something different than true/false in your DB column. If the column is supposed to contain a boolean value, why are you allowing it to contain the null value? A boolean value is either true or false, not null.

Poppycock. It's perfectly OK to have Boolean columns (or any columns) be null-capable if the semantics of the data dictate that.

Best,

4

[...]It's perfectly OK to have Boolean columns (or any columns) be null-capable if the semantics of the data dictate that.

Sure. However, in my experience most people make boolean columns null capable "just because" or because it's the default behavior of the DB when defining columns and after a while problems arise because the returned value is expected to be either true or false, not null: "It is a boolean column, right? Why doesn't it return true or false? Why do I have to worry about the value being null?".

IMHO boolean columns are just a little different because of their very nature. Sure, they can be set to be null but I really don't see the advantage in it. In my experience, if I set the column to default to either true or false (probably false all the time) and change it as needed things work much better and things get easier.

5

pepe wrote in post #962590:

[...]It's perfectly OK to have Boolean columns (or any columns) be null-capable if the semantics of the data dictate that.

Sure. However, in my experience most people make boolean columns null capable "just because" or because it's the default behavior of the DB

[...]

That may be. For myself, I try to consider carefully whether I need null values in a particular field, and what they mean. For booleans, three states are often useful.

IMHO boolean columns are just a little different because of their very nature.

I don't see the difference. Where do you see it?

Sure, they can be set to be null but I really don't see the advantage in it.

I've run across cases where something was best modeled by SQL-style 3-valued logic (for example, yes/no/maybe sorts of things). Why *wouldn't* I use a nullable boolean field for that?

In my experience, if I set the column to default to either true or false (probably false all the time) and change it as needed things work much better and things get easier.

For a true 2-valued boolean, that's also what I do.

Best,

6

I assure you that if my usage could have been modeled with 2 states instead of 3, I would have disallowed “null” :slight_smile:

Three value is very useful. If I have to move this to an integer type, I will (yuck)… but really SQL allows this, the select-box allows this, it works for OTHER data-types. (I can have NULL dates that show up as blank in date_select helpers. I don’t think anyone would suggest that I move to a text type to support a null date, since NO date isn’t a date!).

So, at this point, have we confirmed this is a bug? Even within ruby, we could model this as true/false/nil, right?

7

> IMHO boolean columns are just a little different because of their very > nature.

I don't see the difference. Where do you see it?

> Sure, they can be set to be null but I really don't see the > advantage in it.

I've run across cases where something was best modeled by SQL-style 3-valued logic (for example, yes/no/maybe sorts of things). Why *wouldn't* I use a nullable boolean field for that?

Certainly not very 'academic' but from Wikipedia's page http://en.wikipedia.org/wiki/Boolean_data_type:

"In computer science, the Boolean or logical data type is the most primitive data type, having one of two values (true or false), intended to represent the truth values of logic and Boolean algebra."

Why do I think boolean columns are a little different? You could interpret that null is 'similar' to false because it is not true, but that could also be the case the other way around. If you look a the issue from the perspective of "well, a boolean column is just a column", yes, I agree, there is no difference. However if you look at boolean columns from the perspective of their most likely reason to exist they are different because they imply that they will always contain a value, either true or false and null is neither. That is different IMO than, for example, a date column, which could very possibly be conceived to contain either a date or no date at all. Could a boolean column be conceived to be null? Certainly, but again, not the most likely case in my experience.

Although an interesting proposition I don't think I would ever use a boolean column to store "true/false/maybe sort of things". Of course, the column could be created to allow it but that, IMO, would defeat the purpose of the boolean column. I would rather use a different type because, as it happened to me in the past, a 'true/false/maybe' sort of scenario can quickly become a 'true/false/maybe/likely/not that likely/very likely' sort of scenario and then I would need to change my DB and code.

8

pepe wrote in post #962618:

*wouldn't* I use a nullable boolean field for that?

Certainly not very 'academic' but from Wikipedia's page http://en.wikipedia.org/wiki/Boolean_data_type:

"In computer science, the Boolean or logical data type is the most primitive data type, having one of two values (true or false), intended to represent the truth values of logic and Boolean algebra."

Why do I think boolean columns are a little different? You could interpret that null is 'similar' to false because it is not true,

But I'm not. I think SQL has it right: null is neither false nor true.

but

that could also be the case the other way around. If you look a the issue from the perspective of "well, a boolean column is just a column", yes, I agree, there is no difference. However if you look at boolean columns from the perspective of their most likely reason to exist they are different because they imply that they will always contain a value, either true or false and null is neither.

But that's not even true if we ignore the true 3-valued yes/no/maybe case. The intent of SQL null is to represent *unknown* data. Imagine storing data about your friends, including whether they own a car. You might have a 'has_car' column, which would of course be boolean.

Now, you may not know (because you haven't asked) whether some of your friends own a car or not. So what to put in has_car? You can't put true. You can't put false. Guess what, you put null!

That is different IMO than, for example, a date column, which could very possibly be conceived to contain either a date or no date at all. Could a boolean column be conceived to be null?

Yes, absolutely. See above.

Certainly, but again, not the most likely case in my experience.

But common enough. Don't pretend it doesn't happen.

Although an interesting proposition I don't think I would ever use a boolean column to store "true/false/maybe sort of things".

Why not?

Of course, the column could be created to allow it but that, IMO, would defeat the purpose of the boolean column.

No. SQL booleans are three-valued. It makes sense to use that property.

I would rather use a different type because, as it happened to me in the past, a 'true/false/maybe' sort of scenario can quickly become a 'true/false/maybe/likely/not that likely/very likely' sort of scenario and then I would need to change my DB and code.

Then change it *at that time*. You can't know what will happen in the future, and therefore you can't really design for it. Remember YAGNI. Don't overdesign. Don't anticipate if anticipation does not give you a present benefit.

Best,

9

Ok… lets bring it back around. Forget it says “boolean”, for get all of the baggage that has. There is a column type called “boo” (for the sake of argument) in SQL, that Rails claims to support, but apparently the model is incorrect? (again, have we confirmed this is a bug, and not something I am doing wrong?) and doesn’t fully support the “boo” column type? This seems like a rails bug in the “boo” type.

Interestingly though, it DOES let you set it correctly, and even print it, just when used in a select() box does it choke?

10

> Why do I think boolean columns are a little different? You could > interpret that null is 'similar' to false because it is not true,

But I'm not. I think SQL has it right: null is neither false nor true.

Out of context. I never said it was and 'you' meant 'anybody'. Sorry if it was misleading. Don't take things so personally.

But that's not even true if we ignore the true 3-valued yes/no/maybe case. The intent of SQL null is to represent *unknown* data. Imagine storing data about your friends, including whether they own a car. You might have a 'has_car' column, which would of course be boolean.

Now, you may not know (because you haven't asked) whether some of your friends own a car or not.

That is the first thing I ask my friends. Don't you? :wink:

So what to put in has_car? You can't put true. You can't put false. Guess what, you put null!

I'd put "I didn't ask" but that wouldn't fit in the boolean column... :wink:

Very true, though.

> That is > different IMO than, for example, a date column, which could very > possibly be conceived to contain either a date or no date at all. > Could a boolean column be conceived to be null?

Yes, absolutely. See above.

You have certain ability to repeat yourself.

> Certainly, but again, > not the most likely case in my experience.

But common enough. Don't pretend it doesn't happen.

Not pretending.

> Although an interesting proposition I don't think I would ever use a > boolean column to store "true/false/maybe sort of things".

Why not?

Fragmented. Because of the explanation I gave. Did you read the whole paragraph? (see below).

> Of course, > the column could be created to allow it but that, IMO, would defeat > the purpose of the boolean column.

No. SQL booleans are three-valued. It makes sense to use that property.

I agree with you in the case of *unkown* data.

(From above: you see? this next paragraph explains why. I knew I had written it somewhere!)

> I would rather use a different type > because, as it happened to me in the past, a 'true/false/maybe' sort > of scenario can quickly become a 'true/false/maybe/likely/not that > likely/very likely' sort of scenario and then I would need to change > my DB and code.

Then change it *at that time*.

Sure, and spend who knows how much time changing DB and code if that happens.

You can't know what will happen in the future, and therefore you can't really design for it. Remember YAGNI. Don't overdesign. Don't anticipate if anticipation does not give you a present benefit.

YAGNI is a very good principle to follow and I certainly try to adhere to it as much as possible, however it's not the only thing to take into consideration. There is something else I try to use as much as possible, it's called personal experience. There have been times when because of adhering to YAGNI I had to go back and rework A LOT of code. Now I prefer 'overdesigning' a little bit if I see that there is a possibility that not doing so might come back to bite me, even if the only "present benefit" I get is to have peace of mind and know that I won't get bitten by it. I'd rather spend 30 or 60 extra minutes giving a universal solution to a problem or generating related additional functionality that I might see a use for (but not needed right now) than having to go back and spend days reworking something because of those 'saved' 30 or 60 minutes.

11

Hi David,

I haven't looked at your code, I was just trying to offer a possible reason/solution to keep you going. I'll try to take a look asap but I'm busy right now. However, I am using true/false in an HTML select and it works fine for me.

12

pepe wrote in post #962646:

> Why do I think boolean columns are a little different? You could > interpret that null is 'similar' to false because it is not true,

But I'm not. I think SQL has it right: null is neither false nor true.

Out of context. I never said it was and 'you' meant 'anybody'. Sorry if it was misleading. Don't take things so personally.

I wasn't taking it personally. My point was that if I use null in a boolean column, I do not interpret it as equivalent to false. Others might, but that would be a mistake, as you know.

[...]

So what to put in has_car? You can't put true. You can't put false. Guess what, you put null!

I'd put "I didn't ask" but that wouldn't fit in the boolean column... :wink:

Very true, though.

Right. "NULL" is SQL for "I didn't ask".

[...]

> I would rather use a different type > because, as it happened to me in the past, a 'true/false/maybe' sort > of scenario can quickly become a 'true/false/maybe/likely/not that > likely/very likely' sort of scenario and then I would need to change > my DB and code.

Then change it *at that time*.

Sure, and spend who knows how much time changing DB and code if that happens.

You can't know what will happen in the future, and therefore you can't really design for it. Remember YAGNI. Don't overdesign. Don't anticipate if anticipation does not give you a present benefit.

YAGNI is a very good principle to follow and I certainly try to adhere to it as much as possible, however it's not the only thing to take into consideration. There is something else I try to use as much as possible, it's called personal experience.

Personal experience is valuable, but you can't always know that this design will be just like the last one.

There have been times when because of adhering to YAGNI I had to go back and rework A LOT of code. Now I prefer 'overdesigning' a little bit if I see that there is a possibility that not doing so might come back to bite me, even if the only "present benefit" I get is to have peace of mind and know that I won't get bitten by it.

You know nothing of the sort in most cases. Your actual problems are usually not the ones you anticipated -- after all, if you had anticipated them, you would have anticipated a solution too.

I'd rather spend 30 or 60 extra minutes giving a universal solution to a problem or generating related additional functionality that I might see a use for (but not needed right now) than having to go back and spend days reworking something because of those 'saved' 30 or 60 minutes.

That's a poor trade-off. I know it looks good, but it really isn't. What you're doing is wasting 30-60 minutes for something that you don't need now and may never need.

Barring a few special things like I18N, which really is easier to put in at the start, what takes 30-60 minutes to put in now should take 30-60 minutes to put in two years from now when you actually need it. If that's not the case in your codebase, then you're probably not refactoring enough.

Best,

13

Personal experience is valuable, but you can't always know that this design will be just like the last one.

I don't think about it as a matter of design but just common sense in most cases. I'll explain later.

> I'd rather spend 30 or 60 extra minutes > giving a universal solution to a problem or generating related > additional functionality that I might see a use for (but not needed > right now) than having to go back and spend days reworking something > because of those 'saved' 30 or 60 minutes.

That's a poor trade-off. I know it looks good, but it really isn't. What you're doing is wasting 30-60 minutes for something that you don't need now and may never need.

Just a silly example:

At a place I used to work I was using a much less flexible language than Ruby. This language didn't have by far the capabilities to calculate dates or handle strings that Ruby has or the flexibility to run SQL statements that Rails provides. So during my work there I was tired of hard coding SQL statements all over the place, escaping countless quotes, etc. The code statements were ugly looking and difficult to read. I didn't really 'need' a tool or a system to make the whole thing more flexible, nobody asked for it!, but I got to it and created a system to handle the SQL statements in a much more flexible manner and while I was at it I built additional string and date handling routines that I didn't need at the time. They were initially conceived only to solve the SQL string problems. After 1/2 a day or so of work I had a good additional set of functions that could handle not only the SQL string problems I had but also much more generic string functionality that was natively missing in the language. Think something like 'squeeze'.

Did I need all that? No. Was it nice to work with it after I was done? You betcha! And the funny thing is that after I created all that the other developers started using the string and date functions I had created. So what did the 1/2 day bought me? Personally, not much, maybe satisfaction would be most of it. To the company it meant countless of productivity hours gained, only because I "over- engineered" something I didn't even need to do.

14

pepe wrote in post #962678:

Personal experience is valuable, but you can't always know that this design will be just like the last one.

I don't think about it as a matter of design but just common sense in most cases. I'll explain later.

> I'd rather spend 30 or 60 extra minutes > giving a universal solution to a problem or generating related > additional functionality that I might see a use for (but not needed > right now) than having to go back and spend days reworking something > because of those 'saved' 30 or 60 minutes.

That's a poor trade-off. I know it looks good, but it really isn't. What you're doing is wasting 30-60 minutes for something that you don't need now and may never need.

Just a silly example:

At a place I used to work I was using a much less flexible language than Ruby. This language didn't have by far the capabilities to calculate dates or handle strings that Ruby has or the flexibility to run SQL statements that Rails provides. So during my work there I was tired of hard coding SQL statements all over the place, escaping countless quotes, etc. The code statements were ugly looking and difficult to read. I didn't really 'need' a tool or a system to make the whole thing more flexible, nobody asked for it!,

At that point, you *did* need it. Ugly or repetitive code is an example of a need -- in this case, for refactoring or generalization.

[...]

Did I need all that? No.

Yes you did, by the definition of "need" I"m using.

Best,

15

At that point, you *did* need it. Ugly or repetitive code is an example of a need -- in this case, for refactoring or generalization.

There might have been a 'personal' need but I don't think that makes it a *real* need. The ugly code was just the way it is done. Changing it was like saying "I don't like the way Ruby lets me assign strings to a variable so I am going to change it." and then you come up with something that replaces my_var = 'my string'.

16

Hi David,

Can you post your application in a different format? I need a C compiler to install bzip2 and I'd rather not go into all that.

Thanks.

17

Sorry, I took a break for the weekend, here it is in .tgz format:

http://www.cs.rpi.edu/~crossd/selecttest.tgz

18

Ok… I have been doing some experimentation… and honestly, now I am beginning to get a bit ticked off with how inconsistently this is being handled…

For example, if you replace the f.select() in my example code with:

select_tag(“working”, options_for_select([[“”, “”], [“Yes”, true], [“No”,false]], @testing.working))

it WORKS! (well minus the posting values back to the form and having it update the database since its not in the model…

HOWEVER: f.select (:working, [[“”, “”], [“Yes”, true], [“No”, false]], {:selected => @testing.working})

does NOT work.

REALLY?!

In fact I changed the selections choices to have [“first”, “first”] ahead of the “” choice to test a theory that it wasn’t actually matching anything… and it turns out that’s what its doing, for the case of “false” it doesn’t actually match ANYTHING. you can replace “:selected => false” and it won’t work, it will select “Yes”… go ahead, try it.)… HOWEVER, it works just fine with select_tag?! AUUUGH.

I’ve been through the rails code a couple of times, but I cannot see where this obtuse bug is laying.

19

And.. yes.. I am replying to myself a lot, but I found a workaround that indicates that this IS a bug, it has *NEVER* worked. People just got lucky because of the default order of choices.

To make this work it is as "easy" as: [f.]select([object], :method, [["Yes", true],["No", false]] {:include_blank=>true, :selected=>@object.method.to_s})

@object.method.to_s is the "magic" there. for whatever idiotic, stupid, time-wasting reason, "false" and "nil" are NEVER matching anything. In fact if you remove the ":include_blank" there, and keep that ordering, *false* will show up as "Yes"/true... because 'selected' doesn't get set, since it doesn't match anything in the list.

This is *horribly* broken behavior, and a patch should be made. Someone flips an option somewhere and they could wind up trashing countless data before they realize "false"/nil doesn't actually match anything.

20

David,

I experienced this exact same problem. I echo your sentiments and you are very correct that it is a bug and a big pain. Thanks for posting the problem and a working solution:

Here is my *now* working snippet of code:

<%= f.select :male, [["Male", true],["Female", false]], {:include_blank=>true, :selected=>@user.male.to_s} %>

Thanks again, Sean