Ruby-on-rails 构建 Rails ActiveRecord where 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5820277/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Constructing a Rails ActiveRecord where clause
提问by Kevin Pang
What's the best way to construct a where clause using Rails ActiveRecord? For instance, let's say I have a controller action that returns a list of blog posts:
使用 Rails ActiveRecord 构造 where 子句的最佳方法是什么？例如，假设我有一个返回博客文章列表的控制器操作：
def index
@posts = Post.all
end
Now, let's say I want to be able to pass in a url parameter so that this controller action only returns posts by a specific author:
现在，假设我希望能够传入一个 url 参数，以便此控制器操作仅返回特定作者的帖子：
def index
author_id = params[:author_id]
if author_id.nil?
@posts = Post.all
else
@posts = Post.where("author = ?", author_id)
end
end
This doesn't feel very DRY to me. If I were to add ordering or pagination or worse yet, more optional URL query string params to filter by, this controller action would get very complicated.
这对我来说不是很干燥。如果我要添加排序或分页或更糟的是，更多可选的 URL 查询字符串参数作为过滤依据，则此控制器操作将变得非常复杂。
回答by John Gibb
How about:
怎么样：
def index
author_id = params[:author_id]
@posts = Post.scoped
@post = @post.where(:author_id => author_id) if author_id.present?
@post = @post.where(:some_other_condition => some_other_value) if some_other_value.present?
end
Post.scopedis essentially a lazy loaded equivalent to Post.all (since Post.all returns an array
immediately, while Post.scoped just returns a relation object). This query won't be executed until
you actually try to iterate over it in the view (by calling .each).
Post.scoped本质上是一个延迟加载等价于 Post.all （因为 Post.all 立即返回一个数组，而 Post.scoped 只返回一个关系对象）。在您实际尝试在视图中对其进行迭代（通过调用 .each）之前，不会执行此查询。
回答by Paolo Perego
Mmmh, the best approach you want to use can be to spread this in 2 actions
嗯，您想使用的最佳方法是将其传播到 2 个动作中
def index
@post = Post.all
end
def get
@post = Post.where("author=?", params[:author_id])
end
IMHO it has more sense if you think about a RESTful API, index means to list all and get (or show) to fetch the requested one and show it!
恕我直言，如果您考虑 RESTful API，则它更有意义，索引意味着列出所有并获取（或显示）以获取请求的并显示它！
回答by R Jones
This question is pretty old but it still comes up high in google in 2019, and also some earlier answers have been deprecated, so I thought I would share a possible solution.
这个问题已经很老了，但它在 2019 年在 google 中仍然很高，而且一些早期的答案已被弃用，所以我想我会分享一个可能的解决方案。
In the model introduce some scopes with a test for the existence of the parameter passed:
在模型中引入了一些范围，并测试传递的参数是否存在：
class Post
scope :where_author_ids, ->(ids){ where(author_id: ids.split(‘,')) if ids }
scope :where_topic_ids, ->(ids){ where(topic_id: ids.split(‘,')) if ids }
Then in the controller you can just put as many filters in as you wish e.g:
然后在控制器中，您可以根据需要放入任意数量的过滤器，例如：
def list
@posts = Post.where_author_ids(params[:author_ids])
.where_topic_ids(params[:topic_ids])
.where_other_condition_ids(params[:other_condition_ids])
.order(:created_at)
The parameter can then be a single value or a comma separated list of values, both work fine.
然后参数可以是单个值或逗号分隔的值列表，两者都可以正常工作。
If a param doesn't exist it simply skips that where clause and doesn't filter for that particular criteria. If the param exists but its value is an empty string then it will ‘filter out' everything.
如果参数不存在，则它只是跳过该 where 子句，并且不会针对该特定条件进行过滤。如果参数存在但其值为空字符串，则它将“过滤掉”所有内容。
This solution won't suit every circumstance of course. If you have a view page with several filters on, but upon first opening you want to show all your data instead of no data until you press a ‘submit' button or similar (as this controller would) then you will have to tweak it slightly.
当然，这种解决方案并不适合所有情况。如果您有一个带有多个过滤器的视图页面，但在第一次打开时您想显示所有数据而不是没有数据，直到您按下“提交”按钮或类似按钮（就像这个控制器一样），那么您将不得不稍微调整它.
I've had a go at SQL injecting this and rails seems to do a good job of keeping everything secure as far as I can see.
我已经尝试过 SQL 注入这个，而且就我所见，rails 似乎在保证一切安全方面做得很好。
回答by Rob Di Marco
Would something like this work?
这样的东西会起作用吗？
def get
raise "Bad parameters...why are you doing this?" unless params[:filter].is_a?(Hash)
@post = Post.where(params[:filter])
end
Then you can do something like: ?filter[author_id]=1&filter[post_date]=... etc.
然后你可以这样做： ?filter[author_id]=1&filter[post_date]=... 等等。
回答by Patrick Lafleur
You should model url using nested resources. The expected url would be /authors/1/posts. Think of authors as resources. Read about nested resources in this guide: http://guides.rubyonrails.org/routing.html(scroll to 2.7 - Nested Resources).
您应该使用嵌套资源对 url 建模。预期的 url 将是 /authors/1/posts。将作者视为资源。阅读本指南中的嵌套资源：http: //guides.rubyonrails.org/routing.html（滚动到 2.7 - 嵌套资源）。
