将sql转换为search for rails 3

Question

我有两个模型要通过我的搜索表单进行搜索

<%= form_tag({:controller => 'search', :action => 'search'}, {:method => 'get', :remote => true })  do |select| %>
<%= label_tag :search, "Enter Keywords Here" %>
<%= text_field_tag :search, params[:search] %>

<%= label_tag :country, "Search by Country" %>
<%= collection_select(:country, :country_id, Country.all, :id, :name, :prompt => 'Please Select') %>

<%= label_tag :difficulty, "Search by Difficulty" %>
<%= select_tag :difficulty, options_for_select([['Beginner'],['Intermediate'],['Expert']]), {:prompt => 'Please Select'} %>

<%= label_tag :preperation_time, "Search by preperation time" %>
<%= select_tag :preperation_time, options_for_select([['15..30'],['30..60'],['60..120']]), {:prompt => 'Please Select'} %><br>

<%= submit_tag "Search", :class => "searchbutton" %>
<% end %>

我的理解是，如果我希望能够搜索所有列，我的sql语句应该如下所示

SELECT column dish_name, difficulty, preparation_time FROM Recipe LEFT JOIN Country ON Recipe.dish_name = Country.name AND Recipe.difficulty = Country.name AND Recipe.preparation_time = Country.name

这可能是完全错误的想法，我想要实现的是能够通过一个参数或最多4个参数进行搜索

我不确定如何将其转换为rails语法，目前我有

q = "%#{params[:search]}%"
@countrysearch = Recipe.includes(:country).where("dish_name LIKE ? OR countries.name LIKE ? OR difficulty LIKE ? OR preperation_time LIKE?", q, q, q,q )

如果有人能为我指明正确的方向，我将不胜感激。

Answer 1

不了解的

如果您想更好地理解这个方法调用在做什么，您可以始终使用to_sql来打印生成的SQL查询。

此外，您还可以在控制台中查看被rails降级的日志。

左连接示例

@result = Recipe.joins("LEFT OUTER JOIN countries ON countries.id = recipes.country_id").select

但这也是可能的，更好地做你正在做的事情

@result = Recipe.includes(:country)

打印查询

puts @result.to_sql

构建查询的

countrysearch = Recipe.includes(:country).arel_table
countrysearch = countrysearch.or(countrysearch[:country].eq(params[:search][:country])) if params[:search].has_key?(:country)
#...
countrysearch = countrysearch.or(countrysearch[:difficulty].eq(params[:search][:difficulty])) if params[:search].has_key?(:difficulty)

参考

请在以下参考文献中查找替代方案：

1. rails/arel
2. 215: Advanced Queries in Rails 3

希望这对你有帮助！！