Rails 3 Problem with postgreSQL query on Heroku - boolean vs checkbox parameter = 'on'

Question

I'm getting a postgreSQL error on heroku that I don't get locally on my computer which also uses postgreSQL.

I have a search form with checkboxes and a model with boolean fields. When I check the box to find all reports that are completed, the form passes the parameter value completed='on'. As shown by the heroku logs, the database doesn't like using that parameter value with a boolean in its sql. I would have thought rails would somehow change it from 'on' to true, regardless, the search form works fine locally.

How can I get this to work on Heroku?

Once the website is live on Heroku how do I test future changes if there are issues like this where it works locally but not on the server? Does Heroku give you an area for testing a different version of your app?

Details:

Local Development Log

[1m[35mReport Load (0.8ms)[0m  SELECT "reports".* FROM "reports" WHERE ("reports"."user_id" = 1) AND (completed = 'on') LIMIT 4 OFFSET 0

Heroku Log

ActiveRecord::StatementInvalid (PGError: ERROR:  invalid input syntax for type boolean: "on"
2011-06-08T00:17:21+00:00 app[web.1]: : SELECT  "reports".* FROM "reports" WHERE  ("reports"."user_id" = 2) AND (completed = 'on') LIMIT 4 OFFSET 0):
2011-06-08T00:17:21+00:00 app[web.1]:   app/controllers/reports_controller.rb:6:in `index'

Controller

def index
  @reports = Report.accessible_by(current_ability).search(params)
  @reports = @reports.paginate :per_page => 4, :page => params[:page]
  respond_to do |format|
    format.html
    format.xml  { render :xml => @reports }
  end
end

View

<%= form_tag(reports_path, :method => "get") do %>
<ul>    
    <li>
        <%= link_to 'Show All', reports_path %>
    </li>       
    <li>
    <%= label_tag(:report_number, "Report Number") %>
    <%= text_field_tag(:report_number,nil, :value => params[:report_number]) %>
    </li>
    <li>
    <%= label_tag(:job_number, "Job Number") %>
    <%= text_field_tag(:job_number,nil, :value => params[:job_number]) %>
    </li>
    <li>
    <%= label_tag(:has_failures, "Failures") %>
    <%= check_box_tag(:has_failures,nil, params[:has_failures]) %>
    </li>
    <li>
    <%= label_tag(:completed, "Completed") %>
    <%= check_box_tag(:completed, nil, params[:completed]) %>
    </li>

    <li>
        <%= submit_tag("Search") %>
    </li>                   
</ul>

<% end %>

Model

def self.search(criteria)
 if !criteria[:report_number].blank?
    where("report_number = ?",criteria[:report_number])
  elsif !criteria[:job_number].blank?
    joins(:site).
    where("sites.job_number = ?",criteria[:job_number])
  else
    composed_scope = scoped
    if !criteria[:has_failures].blank?
     composed_scope = composed_scope.where("has_failures = ?",criteria[:has_failures])
   end
   if !criteria[:completed].blank?
      composed_scope = composed_scope.where("completed = ?",criteria[:completed])
   end
   composed_scope
  end
end

Answer 1

This is probably due to a Postgres version mismatch. 'on' became a valid value for boolean fields in Postgres 8.4, so I guess Heroku is still on 8.3. You'll just need to make sure you pass true or false into your query instead.

As far as testing goes, you can indeed setup a staging environment. You can either setup two git remotes for production/staging per this article: http://devcenter.heroku.com/articles/multiple-environments.

Or you can setup a separate staging application and use heroku_san to work with your two applications.

Answer 2

You will need to define the check box's unchecked and checked values, something like this:

check_box(object_name, method, options = {}, checked_value = "1", unchecked_value = "0")