我有一个复杂的数据库查询,它根据房地产列表属性、学校的绩效统计数据以及每个列表与公共交通的距离来选择学校。用户创建一个Search
对象和方法find_schools
在search.rb
有此查询:
School.where(id: school_ids).narrow_schools_for_search(self,prop_type,status,year).joins(listings:
:cta_listings).joins(:performance_stats).where("cta_listings.distance <= ?",
self.cta_distance).where.not(performance_stats: {"#{sort_column.to_sym}" =>
nil}).distinct.limit(30).order("performance_stats.#{sort_column} DESC")
School.rb
scope :narrow_schools_for_search, ->(search,prop_type,status,year) {joins(:listings).joins(:performance_stats)
.where("listings.beds >= ?",search.beds).where("listings.price <= ?",search.max_price)
.where("listings.price >= ?",search.min_price).where(listings: {prop_type: prop_type, status: status})
.where(performance_stats: {year: year}).distinct}
has_many :performance_stats, dependent: :destroy
has_many :assignments, dependent: :destroy
has_many :listings, through: :assignments
Listing.rb
has_many :assignments, dependent: :destroy
has_many :schools, through: :assignments
has_many :cta_listings, dependent: :destroy
has_many :cta_stations, through: :cta_listings
has_many :metra_listings, dependent: :destroy
has_many :metra_stations, through: :metra_listings
PerformanceStat.rb
belongs_to :school
我需要按关联表 PerformanceStats 中的属性排序的 Schools,这是一个用户定义的属性sort_column
。该查询在开发环境 (sqlite3) 中有效,但在暂存应用程序 (PG) 上失败并出现以下错误:
ActiveRecord::StatementInvalid (PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
我需要添加一个 select 语句,其中包含我用来排序学校的列名。
对像这样的其他帖子的建议是执行以下操作:
Widget.select('"widgets".*, "widget_steps.name"')
所以,就我而言,我试过这个:
sort_for_select = "performance_stats.#{sort_column}"
School.select('"schools".*, "#{sort_for_select"').where(id: school_ids).narrow_schools_for_search(self,prop_type,status,year).joins(listings:
:cta_listings).joins(:performance_stats).where("cta_listings.distance <= ?",
self.cta_distance).where.not(performance_stats: {sort_column.to_sym =>
nil}).distinct.limit(30).order("performance_stats.#{sort_column} DESC")
但我的编辑表明我实际上并没有逃到 ruby。无论如何我都试过了,果然,它失败了
ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR: column "#{sort_for_select}" does not exist.
然后我尝试硬编码sort_column
:
School.select('"schools".*, "performance_stats.grall_adjpicalc"').where(id: school_ids).narrow_schools_for_search(self,prop_type,status,year).joins(listings:
:cta_listings).joins(:performance_stats).where("cta_listings.distance <= ?",
self.cta_distance).where.not(performance_stats: {grall_adjpicalc:
nil}).distinct.limit(30).order("performance_stats.grall_adjpicalc DESC")
这适用于开发环境,但如果在登台应用程序上失败并出现此错误:
ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR: column "performance_stats.grall_adjpicalc" does not exist
所以在这一点上,我必须每次都部署来测试新的想法。我知道开发中的 PG 是理想的,但我花了整整一周的时间试图改变并且无法让它工作。最终失去了一切,不得不从头开始重新播种。
我有3个问题:
我在 Select 语句中做错了什么?
有没有另一种快速的方法来避免这个问题?我在想,而不是 Distinct,也许我可以使用 uniq,转换为数组,然后相应地对数组进行排序。
如何将变量sort_column
放入 select 语句中?
任何想法或建议都非常感谢!
更新:我的原始答案中的工作代码很慢,经常在 Heroku 上超时。我最终得到了一个使用 arel_tables 运行速度提高 3 倍的查询。工作代码看起来像这样(self
是一个@search
对象)。我不是专业的编码员,因此毫无疑问这可以执行得更快,我欢迎任何建议以加快性能。根据查询,它仍然需要 5-8 秒。但至少我不再超时了。
school = School.arel_table
pstat = PerformanceStat.arel_table
schools = school.project(Arel.star).join(pstat).on(pstat[:school_id].eq(school[:id]).and(
school[:area_id].in(self.area_ids).and(
pstat[:year].eq(year)
)
)
)
query = schools.to_sql
school_ids = School.find_by_sql(query).pluck(:id)
这为我提供了一系列可能的学校 ID,现在我需要在这些学校中找到与其他搜索参数匹配的列表,包括到公共交通的最大距离(如果选择)。学校和列表通过分配模型连接。
listing = Listing.arel_table
assignment = Assignment.arel_table
if self.cta || self.metra
cta_listing = CtaListing.arel_table
metra_listing = MetraListing.arel_table
listing_assign = listing.join(assignment).on(assignment[:listing_id].eq(listing[:id])).join(cta_listing, Arel::Nodes::OuterJoin).on(cta_listing[:listing_id].eq(listing[:id])).join(metra_listing, Arel::Nodes::OuterJoin).on(metra_listing[:listing_id].eq(listing[:id]))
selected_listings = listing_assign.project(assignment[:school_id], listing[:id]).where(
assignment[:school_id].in(school_ids).and(
cta_listing[:distance].lteq(self.cta_distance).or(
metra_listing[:distance].lteq(self.metra_distance))).and(
listing[:prop_type].in(prop_type).and(
listing[:status].in(status).and(
listing[:beds].gteq(self.beds).and(
listing[:active].eq(true).and(
listing[:price].lteq(self.max_price).and(
listing[:price].gteq(self.min_price))))))))
else
listing_assign = listing.join(assignment).on(assignment[:listing_id].eq(listing[:id]))
selected_listings = listing_assign.project(assignment[:school_id], listing[:id]).where(
assignment[:school_id].in(school_ids).and(
listing[:prop_type].in(prop_type).and(
listing[:status].in(status).and(
listing[:beds].gteq(self.beds).and(
listing[:active].eq(true).and(
listing[:price].lteq(self.max_price).and(
listing[:price].gteq(self.min_price))))))))
end
q = selected_listings.to_sql
listings = Listing.find_by_sql(q)
现在我有了与搜索匹配的所有列表的 AR 关系。在此之前我不能限制在 30 所学校,因为我不确定学校是否会有符合要求的列表。每所学校必须至少有一个列表。我需要返回前 30 所学校及其列表,所以首先我创建一个数组数组,每个列表 id 和相应的 school_id。
listings_array = listings.map{|x| [x.school_id,x.id]}
然后我将这个数组数组转换为一个由 school_id 分组的哈希:
listings_hash = listings_array.group_by{|school_id| school_id.shift}.transform_values do |values|
values.flatten.uniq
end
现在我可以按选定的列对这些学校进行排序并选择前 30 名。
if sort_column.nil?
sort_column = "grall_adjpicalc"
end
schools = School.where(id: listings_hash.keys).includes(:performance_stats).where(performance_stats:
{year: year}).order("performance_stats.#{sort_column} desc").limit(30)
现在我有了我们排名前 30 的学校,并且可以返回一个数组数组,其中包含每个学校及其相应的列表 ID。
schools_array = schools.map{|school| [school,listings_hash[school.id]]}
return schools_array
这比上一个答案要长得多,但至少快了 3 倍。你能找到一种方法来显着加快速度吗?
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句