如何用Chartkick基于多属性求和实现每日营收图表展示？

Hey there, let's figure out how to fix that daily revenue chart issue you're hitting! Let's start by breaking down why your previous attempts failed, then jump to the working solutions.

Why your code broke: #

1. First approach with sum { |li| ... }: When you use a Ruby block with sum alongside group_by_day, you're mixing SQL-level grouping with Ruby-level calculation. The group_by_day generates SQL to group records by date, but the block-based sum tries to run Ruby code on each row—PostgreSQL doesn't understand that, so it throws the grouping error because it doesn't know how to handle unaggregated columns like line_items.id.

2. Second approach reversing sum and group_by_day: You first calculated a single total sum of all revenue (a single BigDecimal value) and then tried to group that by date—obviously, there's no date data left to group on, hence the "Can't group_by n BigDecimal" error.

Fix 1: Database-level calculation (recommended, faster) #

Let the database do the heavy lifting by calculating the revenue per row directly in SQL, then grouping and summing by date. This is way more efficient, especially with large datasets.

In your controller:

# Calculate daily revenue using SQL to multiply price and quantity, then sum per day
@daily_revenue = LineItem.group_by_day(:created_at).sum("store_price * store_fulfillable_quantity")

Then in your view:

<%= line_chart @daily_revenue %>

If store_price is stored as a string (which your to_d suggests), you'll need to cast it to a numeric type in SQL to avoid errors. For PostgreSQL, use:

@daily_revenue = LineItem.group_by_day(:created_at).sum("CAST(store_price AS DECIMAL) * store_fulfillable_quantity")

Fix 2: Ruby-level calculation (for complex logic) #

If you have custom logic that can't be translated to SQL, you can load the records into memory, group them by date, then calculate the sum for each group:

In your controller:

# Group line items by their creation date, then sum revenue for each group
@daily_revenue = @line_items_sold.group_by { |item| item.created_at.to_date }.transform_values do |items|
  items.sum { |item| item.store_price.to_d * item.store_fulfillable_quantity }
end

Then use the same view code:

<%= line_chart @daily_revenue %>

Note: This loads all @line_items_sold into memory, so it's not ideal for very large datasets.

Either of these methods should give you the daily revenue line chart you're looking for!

内容的提问来源于stack exchange，提问作者uno