Rails Queries not Finding Nil Values

I had some queries that were not working and I couldn’t figure out how to make them work.  Basically, I was looking for fields with nil values, so I tried:

Model.where(‘variable = ?’, nil)….and didn’t get any results

So just for the heck of it, I tried:

Model.where(‘variable != ?’, nil)…but still no results.  How is that possible?  It either has to be a value or it isn’t right?

Well, apparently you can’t use normal comparison operands with nil/null.  However, instead of =, you can compare for nil using <=>

So I ended up doing:

Model.where(‘variable <=> ?’, nil)

and it worked, finding the nil values.

Setting Field to Null in MySQL Workbench

I had some bad data in my DB where fields had a space or some other value I could not see, so I needed to set the field to null (my program looks for null).  To do this, I changed the value of the field, then clicked “Apply” and on the SQL preview screen, I changed the value to NULL (ie city = NULL where…).

Lessons Importing CSV’s into PHPMYADMIN

I’ve been working on building dummy data into my local database.  Uploading a CSV that I filled with data using Excel, I populated my database.  Here are two lessons I learned.

Null Integers?
For one of the fields that I didn’t populate with data, imported a 0 because it was an integer field.  However, I needed the field null.  When I went into the record and took out the 0 and tried to save it, I got an error.  What I found was that when I clicked the checkbox for null for the record, it nullified the field.

Date Format
The date format was the European format in my database YYYY-MM-DD.  However, when I had the dates in the American format in my CSV MM-DD-YYYY. The records imported successfully in my database, but instead of erring out with the data, it set the date to 0000-00-00.  So I just had to delete the imported records, and then edit the date format in the CSV and re-import.