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.

Testing Email with RSpec

I ran into two types of errors when testing if emails triggered in the code with RSpec.

The first issue was that RSpec was not correctly determining if an email was triggered.  I knew the email was getting triggered correctly, but my test was failing.  The reason was that I was putting the should matcher too late.


I was putting this after calling the method I was testing (i.e. post :create) like I do with all of my other matchers.  However, with Mailer.should_receive, it should go before the call.

The second issue I had was that I was getting the error message:

Undefined method ‘deliver’ for nil:NilClass

For this, I had to stub out the response, like so:

Mailer.should_receive(:email_name).and_return(double(“Mailer”, :deliver => true))

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