Rails where and group clause

Question

I have collection of Cost records and want to select specific costs and group them by day

i tried this

 @costs = Cost.where(source:(params[:source]),month:params[:month]).group(:day).order('created_at ASC')

but after this i have got ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "costs.id" must appear in the GROUP BY clause or be used in an aggregate function

my goal it's a fetch specific records and group it by day

for example i have few records 15 january i can add more than one record at this day,so i need to group it together

Or may be you can suggest any other solutions

db -psql (PostgreSQL) 9.4.5

rails - Rails 4.2.5

Answer 1

If you want to use group, you have to add a kind of calculation or aggregation functions on the groups. In order to fetch grouped records, try to get grouped ids first, and then you can fetch only records with given ids:

groups = Cost.group(:day).select('array_agg(id) as ids, day').limit(100).to_a
last_day_group = groups.last

# now you can get ids array and day value
puts last_day_group.day
 # => 'Monday'
puts last_day_group.ids
 # => [1, 2, 3, 45]

# get Cost records by day: 
Cost.where(id: last_day_group.ids)

Answer 2

This is because you are using the group by in a wrong way. You code above will be translated by Rails into sql statement, which is

select * from cost where source = value and month = value group by day order by created_at asc

This is an invalid sql statement because you are selecting all fields in the table and none of them either part in any of any aggregate function or the group by clause.

Please check this link out for more info about how to write correct group by clause.

here is an example of a Rails code translated correctly to sql statement contain the usage of group by clause.

Order.select("COUNT(id) as num, payment_method, purchase_price, selling_price, xcount").where("multiplex_id = ? AND created_at >= ? AND created_at <= ? AND state = ?", multiplex_id, start_date, end_date, 'reserved').group(:payment_method, :purchase_price, :selling_price, :xcount)

As you can see I have specified all the fields that I need to retrieve and all these fields are either part of aggregate function or used by group by clause.