gpt4 book ai didi

mysql - rails : mysql error unknown column in sub query

转载 作者:行者123 更新时间:2023-11-29 07:37:58 29 4
gpt4 key购买 nike

我正在我的网络应用程序中实现搜索过滤器。使用这样的子查询:

tool = Tool.select('*, (select ROUND(AVG(ratings.rating)) from ratings where tool_id = tools.id AND rating_type = 2) as ratings, 

3956 * 2 * ASIN(SQRT(POWER(SIN(('+"#{params[:latitude]}"+' - abs(tools.latitude)) * pi()/180 / 2), 2) + COS('+"#{params[:latitude]}"+' * pi()/180 ) * COS(abs(tools.latitude) * pi()/180) * POWER(SIN(('+"#{params[:longtude]}"+' - abs(tools.longitude)) * pi()/180 / 2), 2) )) as distance').where('user_id != ? AND pause_status =?', user_id, 0).order('distance asc')

# =>delivery type only if delivery type is 1
if params[:search].present? && !params[:search].nil?
tool = tool.where('title LIKE ? OR description LIKE ?', "%#{params[:search]}%","%#{params[:search]}%")
end

# =>Category search
if params[:category_id].present? && !params[:category_id].nil?
tool = tool.where('category_id =?', params[:category_id])
end

# =>price range
if params[:max_price].present? && params[:min_price].present? && !params[:max_price].nil? && !params[:min_price].nil?
tool = tool.where('price >= ? AND price <= ?', params[:min_price].to_f, params[:max_price].to_f)
end

# => filter availability
if params[:availability].present? && !params[:availability].nil?
if params[:availability].to_i == 2
tool = tool.where('available_type =?', 2) #=> weekend
elsif params[:availability].to_i == 1
tool = tool.where('available_type =?', 1) # => weekdays
end
end

if params[:rating].present? && !params[:rating].nil?
tool = tool.having('ratings > 5')
end


if params[:delivery_type].present? && !params[:delivery_type].nil?
if params[:delivery_type].to_i == 0
tool = tool.where('delivery_type = ?', 0)
end
end

if tool.empty?
return []
else
tool_array = []
tool.each do |t|
tool_hash = {}
tool_hash['id'] = t.id
tool_hash['title'] = t.title
tool_hash['latitude'] = t.latitude
tool_hash['longitude'] = t.longitude
tool_hash['attachment'] = Image.get_single_attachment(t.id)
tool_array.push(tool_hash)
end
return tool_array
end

当我传递评分参数时,它会像这样打印查询:

SELECT COUNT(*) FROM `tools` WHERE (user_id != 3 AND pause_status =0) HAVING (ratings > 5)"

没有评级参数:

SELECT *, (select ROUND(AVG(ratings.rating)) from ratings where tool_id = tools.id AND rating_type = 2) as ratings, 3956 * 2 * ASIN(SQRT(POWER(SIN((30.657797735213 - abs(tools.latitude)) * pi()/180 / 2), 2) + COS(30.657797735213 * pi()/180 ) * COS(abs(tools.latitude) * pi()/180) * POWER(SIN((76.7327738833397 - abs(tools.longitude)) * pi()/180 / 2), 2) )) as  distance FROM `tools` WHERE (user_id != 3 AND pause_status =0) ORDER BY distance asc"

我的 having 子句中出现这样的错误:

"error": "Mysql2::Error: Unknown column 'ratings' in 'having clause': SELECT COUNT(*) FROM `tools` WHERE (user_id != 3 AND pause_status =0) HAVING (ratings > 5)",
"code": 301

如果我评论每个循环,它就会起作用。请告诉我哪里做错了。

最佳答案

having 应该与 group by 一起工作

关于mysql - rails : mysql error unknown column in sub query,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47881145/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com