Rails App Plotting Zeros in Highchart Graph via Lazy_High_Charts Gem

Question

I'm still very new to Rails but moving along fairly smoothly I would say. So for practice I'm working on what's supposed to be a simple application where a user can input their weight and that info, over a 30 day period, is displayed to them via a Highcharts line graph using the Lazy Highcharts gem. I followed Ryan Bates' Railscast #223 to get started.

My Issue:
Ok, so the inputs are showing up except that on the days that a user doesn't input a value it gets displayed on the chart as '0' (the line drops to bottom of the graph), instead of connecting to the next point on any given day. Not sure if all that makes sense so here's a screenshot:

I found this solution: Highcharts - Rails Array Includes Empty Date Entries

However, when I implement the first option found on that page (convert 0 to null):

(30.days.ago.to_date..Date.today).map { |date| wt = Weight.pounds_on(date).to_f; wt.zero? ? "null" : wt }

the points show up but the line does not, nor do the tooltips...this leads me to think that something is breaking the js. Nothing is apparently wrong in the console though..? From here I thought it might be a matter of using Highchart's 'connectNulls' option but that didn't work either. When implementing the second option (reject method):

(30.days.ago.to_date..Date.today).map { |date| Weight.pounds_on(date).to_f}.reject(&:zero?)

it completely removes all dates that are null from the chart, which messes up the structure completely because the values are supposed to be displayed based on their created_at date.

So back to square one, this is what I'm left with (chart plotting zeros for days without inputs).

Model:

class Weight < ActiveRecord::Base
  attr_accessible :notes, :weight_input

  validates :weight_input, presence: true
  validates :user_id, presence: true

  belongs_to :user

  def self.pounds_on(date)
    where("date(created_at) = ?", date).pluck(:weight_input).last
  end
end

Controller:

def index
  @weights = current_user.weights.all

  @startdate = 30.days.ago.to_date
  @pounds = (30.days.ago.to_date..Date.today).map { |date| Weight.pounds_on(date).to_f }

  @h = LazyHighCharts::HighChart.new('graph') do |f|
    f.options[:title][:text] = " "
    f.options[:chart][:defaultSeriesType] = "area"
    f.options[:chart][:inverted] = false
    f.options[:chart][:zoomType] = 'x'
    f.options[:legend][:layout] = "horizontal"
    f.options[:legend][:borderWidth] = "0"
    f.series(:pointInterval => 1.day, :pointStart => @startdate, :name => 'Weight (lbs)', :color => "#2cc9c5", :data => @pounds )
    f.options[:xAxis] = {:minTickInterval => 1, :type => "datetime", :dateTimeLabelFormats => { day: "%b %e"}, :title => { :text => nil }, :labels => { :enabled => true } }
  end

  respond_to do |format|
    format.html # index.html.erb
    format.json { render json: @weights }
  end
end

Does anyone have a solution for this? I guess I could be going about this all wrong so any help is much appreciated.

Answer 1

This is a HighCharts specfic. You need to pass in the timestamp into your data array vs. defining it for the dataset.

For each data point, I set [time,value] as a tuple.
[ "Date.UTC(#{date.year}, #{date.month}, #{date.day})" , Weight.pounds_on(date).to_f ]

You will still need to remove the zero's in w/e fashion you like, but the data will stay with the proper day value.

I do think you need to remove :pointInterval => 1.day

General tips You should also, look at optimizing you query for pound_on, as you are doing a DB call per each point on the chart. Do a Weight.where(:created_at => date_start..date_end ).group("Date(created_at)").sum(:weight_input) which will give you an array of the created_at dates with the sum for each day.

ADDITIONS

Improved SQL Query

This leans on sql to do what it does best. First, use where to par down the query to the records you want (in this case past 30 days). Select the fields you need (created_at and weight_input). Then start an inner join that runs a sub_query to group the records by day, selecting the max value of created_at. When they match, it kicks back the greatest (aka last entered) weight input for that given day.

@last_weight_per_day = Weight.where(:created_at => 30.days.ago.beginning_of_day..Time.now.end_of_day)
select("weights.created_at , weights.weight_input").
joins(" 
  inner join (  
      SELECT weights.weight_input, max(weights.created_at) as max_date 
      FROM weights 
      GROUP BY weights.weight_input , date(weights.created_at) 
  ) weights_dates on weights.created_at =  weights_dates.max_date
")

With this you should be able @last_weight_per_day like so. This should not have 0 / nil values assuming you have validated them in your DB. And should be pretty quick at it too.

  @pounds = @last_weight_per_day.map{|date| date.weight_input.to_f}