Rails 4 + Filtration of Data on the basis of Day, Week, Month and Year in HighCharts

Question

In my Sqlite table, I have more than 5 years records for price and quantity of an Product. Now I am trying to render data in High Charts. For Filtration part, I am putting 4 radio button i.e, Day, Week, Month and Year.

If User click on Day selection, then Graph should render the day wise data. If User clicks on Week button, then Graph should render the weekly calculated value and so on.

Now I am not getting the way to implement it. I trying to implement the filter code in MODEL.

enter image description here

Please suggest something, how to proceed.

score 3 · Answer 1 · edited May 23 '17 at 10:26

This solution is quick and dirty, and might have some performance issues. I'll suggest potential optimizations down below.

Assuming a Product model with the following schema,

# Table name: products
#
#  id         :integer          not null, primary key
#  name       :string
#  amount     :float
#  quantity   :integer
#  created_at :datetime         not null
#  updated_at :datetime         not null

in models/product.rb:

class Product < ActiveRecord::Base
  def self.group_by_day
    # queries the database 
    self.all.group_by { |p| p.created_at.beginning_of_day }
    # if you're using a different date field, replace 'created_at' with that field.
  end

  def self.quantity_by_day
    # returns an array of hashes, each containing the date and quantity
    quantity_array = []
    self.group_by_day.each do |day, products|
      quantity_array << {
        date: day.to_s(:db), 
        quantity: products.map(&:quantity).sum
      }
    end
    quantity_array
  end

  def self.group_by_week
    self.all.group_by { |p| p.created_at.beginning_of_week }
  end

  def self.quantity_by_week
    # ...
  end

  def self.group_by_month
    self.all.group_by { |p| p.created_at.beginning_of_month }
  end

  def self.quantity_by_month
    # ...
  end

end

Product.quantity_by_day.to_json will give you the data which you can use for the chart. Here's a JSFiddle of a HighCharts chart using the data. (~~I don't know why the X-axis dates aren't working for me, but the chart shows correctly.~~edit: fixed it.)

As I mentioned earlier, querying all the products and grouping them in Ruby is inefficient. I would suggest:

Using PostgreSQL instead of SQLite, then use the groupdate gem. This uses SQL to group the records, which is a lot faster. SQLite does not have good date support, so we have to use group_by
Also, see this SO question about grouping by week/month in SQL

Lastly, showing your Products table schema would help us come up with a more specific answer.

Rails 4 + Filtration of Data on the basis of Day, Week, Month and Year in HighCharts

1 Answers1