GroupDate doesn't work with Postgresql databases

[Tectract]

Hi there!

Great tool, but I ran into issues trying to run it on my postgresql systems.

Order.group_by_day(:created_at)

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "orders.id" must appear in the GROUP BY clause or be used in an aggregate function

It appears this is a quirk of how the .group statement works in postgresql. Any thoughts on how to get around it?

[Tectract]

OK, I found a working query for orders_by_day for my shopping cart application... it varies from your queries a bit...

your query form:

orders.where(:created_at => start_time.beginning_of_day..Time.zone.now.end_of_day)
.group("date(created_at)")
.select("date(created_at), sum(total_price) as total_price")

my new query form:

orders.where(:created_at => start_time.beginning_of_day..Time.zone.now.end_of_day)
.group("date(created_at)").
.select("date(created_at) as created_at, array_agg(orders.id), sum(total_price) as total_price")

This fixes the problem and allows me to elegantly group/select and sum order totals within date ranges for my graphs, as the gem is supposed to do... but within postgresql

[ankane]

Hey @Tectract, you need to specify an aggregate method like count, sum, average, etc:

Order.group_by_day(:created_at).count

Groupdate's methods behave the same as Active Record's group method - replace group_by_day with group and you'll see the same error:

Order.group(:created_at) # same error

[Tectract]

.sum wasn't working for me, which is what I needed.

[dustineichler]

@Tectract you can find a possible resolution here:

#246 (comment)

[Tectract]

Here was my solution for group_by_week, for reference... I looked at your code, this is sort of what I expected to see, lol.

def orders_chart_series_weekly_totals(orders,start_time)
    if orders == nil
      orders = Order.all
    end
    totals = {}
    orders_by_day = orders.where(:created_at => start_time.beginning_of_day..Time.zone.now.end_of_day)
      .group("date_trunc('week',created_at)")
      .select("date_trunc('week',created_at) as created_at, array_agg(orders.id), sum(orders.order_total) as order_total")
    (start_time.to_date.beginning_of_week..Date.today.end_of_week).step(7).map do |date|
      order = orders_by_day.detect { |order| order.created_at.to_date == date }
      totals["#{date}"] = order && order.order_total.to_f/100 || 0.0
    end
    return totals
  end

[ankane]

Glad you found something that works.