Rails hash key/value created differently in local vs heroku

Question

I ran into this issue late last night and I just figured out what happened, but I want to understand why. I tested my code in my local (using sqlite) and it worked fine, but when I deployed to heroku (postgres), a summary table was not showing the right results. The summary data is being retrieved from a hash that is created by querying active record, converting to row and then to hash, resulting in a key, value pair hash, let's call that @assignments. The method used to create that result is the following:

 def summarize_hrs(date,userid)
    @query = "SELECT id, (coalesce(hrs1,0) + coalesce(hrs2,0) + coalesce(hrs3,0)) as hrs
                        FROM
                        ((
                            (
                            jobs
                            left outer join
                            (SELECT job1, sum(coalesce(hrs1,0)) as hrs1 FROM schedules WHERE date = '" + date.to_s + "' and job1 is not null and user_id =" + userid.to_s + " group by job1) s1
                            on jobs.id = s1.job1
                            ) sum1
                        left outer join
                                (SELECT job2, sum(coalesce(hrs2,0)) as hrs2 FROM schedules WHERE date = '" + date.to_s + "' and job2 is not null and user_id =" + userid.to_s + " group by job2) s2
                                on sum1.id = s2.job2) sum2
                             left outer join
                            (SELECT job3, sum(coalesce(hrs3,0)) as hrs3 FROM schedules WHERE date = '" + date.to_s + "' and job3 is not null and user_id =" + userid.to_s + " group by job3) s3
                            on sum2.id = s3.job3)"
    @assignments = Hash[Schedule.connection.select_all(@query).rows]    
    return @assignments     
  end

As a troubleshooting step I started printing the @assignment contents to the screen and this is what I got:

local (sqlite): {1=>0, 2=>0, 3=>9.0, 4=>0, 5=>0} prod (heroku): {"1"=>"11", "2"=>"7", "3"=>"0", "4"=>"7"}

The results are for different users thus they are not going to match; however what's interesting is that the heroku hash has "" and the local one does not. Why is this, is there anything I'm doing wrong in the query/conversion.

On the interim I've fixed this by forcing my prod key to be a string (shown below), but this breaks my local. Any help will be much appreciated

 </tr>
      <% @total_planned = 0 %>
      <% @jobs.each do |job| %>
      <% @search = "Select "+ @col+" FROM schedule_plans where job_id= "+job.id.to_s+" and user_id = "+@userid.to_s 
       @scheduled_hrs = SchedulePlan.connection.select_value(@search).to_f  %>
      <tr>
        <% if (@scheduled_hrs > 0) %>
        <% @total_planned = @total_planned + @scheduled_hrs %>
        <% @assigned_hrs = @assignments[job.id.to_s].to_f %>
        <td> <%= job.name %> </td>
        <td> <%= @assigned_hrs %> </td>
        <td> <%= @scheduled_hrs %> </td>
        <td class = "variance" > <%= @scheduled_hrs - @assigned_hrs%></td>
        <%end%>
      </tr>

score 0 · Accepted Answer · edited May 23 '17 at 11:43

0

This looks to be a difference in how the pg Gem and underlying libpq driver handle typing vs. the SQLite driver, stemming from a deliberate decision by the driver developers to leave type conversion to the application framework and return everything as a string. By executing raw SQL and going straight from Result.rows to Hash you are by-passing logic in ActiveRecord to map types, so you get the type chosen by the driver.

See:

edited May 23 '17 at 11:43

Community

1
1

answered Jun 03 '15 at 18:39

bimsapi

4,985
2
19
27

Thank you, this makes a lot of sense. Do you have any thoughts on how to not bypass the ActiveRecord logic to map types? AciveRecordResult.to_hash returned something that wasn't what I was looking for since it included column names as the key. – Andrea Jun 04 '15 at 02:19
I don't have a direct answer to that (my knowledge of Rails is fairly weak). What I did come up with was something like this: `@assignments = rst.rows.collect {|k,v| {k.to_i => v.to_i}}` where `rst` is the result from `Schedule.connection.select_all(@query)`. It adds an additional step, but it should work across sqlite and PostgreSQL. My recommendation, though, would be to look into using PostgreSQL in development to avoid other hiccups like this. – bimsapi Jun 04 '15 at 14:36
I ended up fixing this by creating a SQL view to summarize the data without affecting type cast – Andrea Jun 22 '15 at 01:22

Rails hash key/value created differently in local vs heroku

1 Answers1