Constructing a Rails ActiveRecord where clause

Question

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:

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:

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.

Answer 1

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

Answer 2

Mmmh, the best approach you want to use can be to spread this in 2 actions

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!

Answer 3

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.

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.

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.

Answer 4

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.

Answer 5

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