Changing Column to Add Precision and Scale

If you have to add precision and scale to a decimal field, here’s a sample migration:


class ChangeScale < ActiveRecord::Migration
def self.up
change_column :shirts, :price, :decimal, precision: 5, scale: 2

def self.down
change_column :shirts, :price, :decimal, :precision, :scale

Sorting Nil Results to End when Order Ascending (ASC)

I had to sort a bunch of records with order ascending, but the problem is that some of these records will occasionally be nil.   For my use case, I needed the nils to be listed at the end, with the non-nils sorted in ascending order.

So I had something like this:

@bars = @foo.bars.order(“number_of_votes ASC”)

where I wanted the articles to show in the order of votes they received, but I wanted the ones with a nil number of votes to come last.

I found the answer in this Stack Overflow question using coalesce.

(1) First, I needed to determine the number of records (i.e. count = @foo.bars.count)

(2) Then I adjusted my code to:

count = @foo.bars.count

@bars = @foo.bars.order(“coalesce(number_of_votes, #{count}) ASC”)

This treated all of the nil values as if it had a number_of_votes, placing them at the end of the array.

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.