I'm trying to use chartkick to display a multi series line chart which tracks total Inbound and Outbound Material flow by month over the past year.
The queries should generate something similar to this:
SELECT SUM(ship_total_net_weight) AS sum_ship_total_net_weight,
month(ship_date) AS month_ship_date, year(ship_date)
AS year_ship_date
FROM [Downstream]
WHERE is_active = 1 AND from_company_id = 89
AND
(ship_date BETWEEN '2014-06-15 18:15:26.196'
AND '2015-06-15 18:15:26.196')
AND (to_company_id <> 89)
GROUP BY month(ship_date), year(ship_date)
order By year(ship_date), month(ship_date)
My chart kick code is as follows:
<%= line_chart [
{name: "Inbound", data: Downstream.where(:to_company_id => current_user.company_id, :ship_date => 1.year.ago..Time.now).where('from_company_id <> ?', current_user.company_id ).group('month(ship_date), year(ship_date)').sum(:ship_total_net_weight)},
{name: "Outbound", data: Downstream.where(:from_company_id => current_user.company_id, :ship_date => 1.year.ago..Time.now ).where('to_company_id <> ?', current_user.company_id ).group('month(ship_date), year(ship_date)').sum(:ship_total_net_weight)}
] %>
The result I get from chart kick is a line for each series with just two data points, one on Dec 31, 2013 and one on Dec 31, 2014.
Running the above SQL against the database generates 9 results ranging from 6/2014 to 2/2015
Why is the dataset different? Is my syntax wrong for chart kick? Do I need to change the query somehow?
Thanks for the help in advance!
I found the solution, my query was returning separate date/month columns so I needed to change the group clause, the following line chart works correctly:
Using the dateadd and datediff functions in the group clause allows sorting by month/year of a date or date time field and returns the full date in a single column.
-Side note - I'm using SQLServer which is not supported by the groupdate gem that's often used on conjunction with chartkick.