Rails Postgres查询，仅选择出现在所有具有关联的搜索参数中的项目 [英] Rails Postgres query, selecting only items that appear in all search params with associations

问题描述

我希望基于一些用户选择参数来创建Postgres查询。

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.

例如，用户选择开始日期为2014年，结束日期为2018年。项目香蕉在所有年份中均具有特征，但苹果仅在2014年，2015年和2017年具有特征。因此，我的最终结果将仅显示香蕉的平均价格，而不显示苹果。

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')

模式

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

推荐答案

您可以检查每年有哪些记录。您可以通过检查每个项目的不同年份数是否等于年份总数来做到这一点（使用 COUNT DISTINCT ）：

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})")

我也使用 BETWEEN 代替< 和> 。
我想您想按商品名称而不是商店进行分组（就像您原来的查询中那样）。

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).

这篇关于Rails Postgres查询，仅选择出现在所有具有关联的搜索参数中的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！