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.
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…).
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.
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.
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.