Grouping failing

[tobiasgr]

Hi guys

I've run into a strange error here.

I am trying to do this: Lead.group_by_month(:created_at).count

And get this error (maybe its related to the "timestamptz" parts? I have not noticed that before):

PG::GroupingError: ERROR: column "leads.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...agen')) AT TIME ZONE 'Europe/Copenhagen' ORDER BY "leads"."c...
^
: SELECT COUNT(*) AS count_all, (DATE_TRUNC('month', (created_at::timestamptz) AT TIME ZONE 'Europe/Copenhagen')) AT TIME ZONE 'Europe/Copenhagen' AS date_trunc_month_created_at_timestamptz_at_time_zone_europe_cop FROM "leads" WHERE (created_at IS NOT NULL) GROUP BY (DATE_TRUNC('month', (created_at::timestamptz) AT TIME ZONE 'Europe/Copenhagen')) AT TIME ZONE 'Europe/Copenhagen' ORDER BY "leads"."created_at" DESC

My setup:
Rails 5.1.5
Groupdate 4.0.1
pg 0.21.0
Postgressql: 9.6

[ankane]

Check out #100

[tobiasgr]

Thank you!! Indeed worked removing the sorting order :)

[marcamillion]

I get a similar error, but I have no sorting order on my model.

My models are as follows:

# == Schema Information
#
# Table name: portfolios
#
#  id                   :bigint(8)        not null, primary key
#  user_id              :integer
#  current_dollar_value :float            default(0.0)
#  dollar_change        :float            default(0.0)
#

class Portfolio < ApplicationRecord
  belongs_to :user
  has_many :port_stocks
end

# == Schema Information
#
# Table name: port_stocks
#
#  id             :bigint(8)        not null, primary key
#  portfolio_id   :integer
#  stock_id       :integer
#  volume         :integer
#

class PortStock < ApplicationRecord
  belongs_to :portfolio
  belongs_to :stock
end

I have tried the following, both of which don't work and give me the same error:

<%= pie_chart PortStock.group("portfolio_id") %>

and

<%= pie_chart @portfolio.port_stocks.group(:current_value) %>

This is the error I get:

PG::GroupingError: ERROR:  column "port_stocks.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "port_stocks".* FROM "port_stocks" WHERE "port_stocks...
               ^
: SELECT "port_stocks".* FROM "port_stocks" WHERE "port_stocks"."portfolio_id" = $1 GROUP BY "port_stocks"."current_value"

Or

PG::GroupingError: ERROR:  column "port_stocks.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "port_stocks".* FROM "port_stocks" GROUP BY "port_sto...
               ^
: SELECT "port_stocks".* FROM "port_stocks" GROUP BY "port_stocks"."portfolio_id"

Rails 5.2.0
Ruby 2.5.1
Chartkick 2.3.4
Groupdate 4.0.1

Thoughts?

Edit 1

I also tried the following, after reading #100 :

<%= pie_chart @portfolio.port_stocks.group(:current_value).unscope(:order) %>

But that still produces the following error:

PG::GroupingError: ERROR:  column "port_stocks.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "port_stocks".* FROM "port_stocks" WHERE "port_stocks...
               ^
: SELECT "port_stocks".* FROM "port_stocks" WHERE "port_stocks"."portfolio_id" = $1 GROUP BY "port_stocks"."current_value"

Edit 2

For what it's worth, I tried this:

<%= pie_chart @portfolio.port_stocks.group(:current_value).count %>

And that worked.....I can't say why though.

[marcamillion]

To address my above comment, I actually figured it out which you can see the resolution on Stack Overflow here: https://stackoverflow.com/questions/50243457/how-do-i-get-associations-working-right-with-chartkick/50245478#50245478

[ankane]

@marcamillion see #166