CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-5
DollarChills
DollarChills

Reputation: 1086

Rails 5 grouping by belongs_to association

I have a sale_selection model which belongs_to both purchase and sale.

In a Postgres query i'm trying to search by some params, grouping the sale item_name and then getting the average price.

I'm getting the following error:

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "sale"

Controller

@search = Purchase.where('extract(year from sale_year) >= ?', params[:select_year])
.joins(:region).where('regions.name = ?', params[:select_region])
.joins(sale_selections: :sale).group('sale.item_name').select('sale.item_name, AVG(sale.price) as price')

Upvotes: 0

Views: 56

Answers (2)

Vishal
Vishal

Reputation: 7361

Correction in table name provided in query is needed (sales instead of sale) as below,

@search = Purchase.where('extract(year from sale_year) >= ?', params[:select_year])
.joins(:region).where('regions.name = ?', params[:select_region])
.joins(sale_selections: :sale).group('sales.item_name').select('sales.item_name, AVG(sales.price) as price')

Upvotes: 1

Deepak Mahakale
Deepak Mahakale

Reputation: 23671

Assuming you are following rails best practices table name should be sales instead of sale

@search = Purchase.where('extract(year from sale_year) >= ?', params[:select_year])
.joins(:region).where('regions.name = ?', params[:select_region])
.joins(sale_selections: :sale).group('sales.item_name').select('sales.item_name, AVG(sales.price) as price')

Upvotes: 1

Related Questions