Rails Postgres query, selecting only items that appear in all search params with associations

I’m looking to create a Postgres query based on a few user select params.

A user will select a shop and a start and end year. Once submitted I’m would only like to display average prices of items if they only exist in all years selected.

For example, a user selects a start date of 2014 and end date of 2018. Item banana features in all those years, but apple only features in 2014, 2015 and 2017. So my end result will only show the average price of bananas and not apples.

So far, this is the query I have developed. I’m unsure on how to best implement the part where only the item appearing in all search years is averaged.

@sale_averages = Sale.joins(:shops, :items).where('extract(year from season_year) < ? AND extract(year from season_year) > ? ', params[:start_year], params[:end_year])
.where('shops.name = ?', params[:select_shop])
.select('items.name, AVG(sale.price) as price').group('shop.name')

Schema

create_table "items", force: :cascade do |t|
 t.string "name"
end

create_table "sales", force: :cascade do |t|
 t.integer "shop_id"
 t.integer "item_id"
 t.date "season_year"
 t.decimal "price"
end

create_table "shops", force: :cascade do |t|
 t.string "name"
end

Answer

You could check for what items there is a record for every year. You can do that by checking if the number of distinct years for every item is equal to the total of years (using COUNT DISTINCT):

 number_years = params[:end_year].to_i - params[:start_year].to_i + 1
 @sale_averages = Sale.joins(:shops, :items)
                      .select('items.name, AVG(sale.price) as price')
                      .where("EXTRACT(year from season_year) BETWEEN #{params[:start_year]} AND #{params[:end_year]}")
                      .where('shops.name': params[:select_shop])
                      .group('items.name')
                      .having("(COUNT(DISTINCT(EXTRACT(year from season_year))) = #{number_years})")

I have also used BETWEEN instead of < and >. I think you want to group by item name instead of shop (as it was in you original query).