Using scopes with has_many and belongs_to

Implementing scopes on your associations can cause much confusion and frustration; especially when you see hard to interpret SQL-y errors being returned, (you’ve probably seen error messages which start with PG::UndefinedTable: ERROR: missing FROM-clause entry for table..., right?) and have no idea how to go about fixing them.

When you have a belongs_to or a has_one/has_many (with or without :through) association defined on your model, you will in many situations benefit from defining scopes in your model that reference your association. Defining a custom scope which references attributes in your association table(s) has the potential to DRY up your code and make code in other parts of your application easier to understand.

Defining scopes directly on the association

ActiveRecord's has_many and belongs_to methods give you the ability to define scopes directly on the association. This way, you can customize the SQL that is generated when you access the association.

Say you have a User model which has_many posts, and when you do @user.posts, you only want to return the posts which were created in the last year. Your User model would look like:

class User < ActiveRecord::Base
  has_many :posts, -> { where('created_at > ?', Time.current - 1.year) }

Because you’ve defined the scope this way, when you do @user.posts, the generated query will include the where condition above so that any posts returned will have been created within the past year.

You can also pass in an argument to this scope, which allows you to further customize the generated query. Say instead of defaulting to 1 year you wanted to pass in the timeframe, you could do something like:

class User < ActiveRecord::Base
  has_many :posts, ->(timeframe) { where('created_at > ?', timeframe }

One gotcha to keep in mind, especially when using columns like created_at which are common to most tables, is that you might have to specify the table name explicitly in your scope if your DB complains.

So the above would look like:

class User < ActiveRecord::Base
  has_many :posts, ->(timeframe) { where('posts.created_at > ?', timeframe) }

Referencing attributes of the association table in your scope

You might also sometimes benefit from defining scopes in the parent model which reference attributes of your association. Adding to the example above, let’s say you wanted to define a scope on the User model which returns all users who have pending posts (posts which haven’t been “approved” yet).

class User < ActiveRecord::Base
  has_many :posts
  scope :with_pending_posts, -> { joins(:posts).where('posts.pending = true') }

Couple of things to note from the scope definition above:

  1. You have to use the joins method in your scope so that your DB knows which table you’re referring to.
  2. To reference association attributes in the where method, you have to use the table name of the association followed by the column name (posts.pending in this case) to prevent your DB from complaining, and to have it return the expected result.

A corollary from the points above is that you can technically reference attributes of any table in your scope, even if they are not explicitly defined as an association in your model. You’d of course have to set up your joins correctly – refer to the ActiveRecord documentation for more info, and if you’re still stuck, ping me in the comments below!

Update: Kevin pointed out in the comments section that instead of doing:

scope :with_pending_posts, -> { joins(:posts).where('posts.pending = true') }

you could do:

scope :with_pending_posts, -> { joins(:posts).merge(Post.pending) }

This is definitely worth a consideration as it makes your code cleaner and easier to read. Not only that, you will probably be able to make use of the scope you define on your Post model in other areas of your system.

If you haven’t seen the merge method before, take a look at the following resources to get acquainted and some ideas on how it could be useful:

  1. ActiveRecord documentation on merge
  2. A useful article via on using merge with scopes
  3. An article I wrote on 4 ways to filter has_many associations, one of which includes using the merge method

Troubleshooting error messages

When the scope you’re building is complex, you’ll probably run into errors, especially SQL errors. It will benefit you greatly to learn how to read these errors and understand what your DB is complaining about. Typical complaints include tables which are not defined and columns which don’t exist because the query is looking for them in the wrong table.

Remember that a custom scope is the same as defining a class method. So if you’re stuck, ask yourself how you would implement the scope as a class method.

Keep in mind that if you want your scope to do calculations based on association attributes, you might not be able to use Ruby, and will have to brush up on your SQL (more importantly, SQL that works with your DB of choice) to accomplish what you need.

For example, check out the scope below, where for an Availability model, we’re returning records whose start_time is greater than an associated Venue's notice_time, which in this case happens to be stored as an integer. As you’ll notice, Now() and 1 hour::interval are Postgres specific SQL.

class Availablity < ActiveRecord::Base
  has_one :venue
  scope :after_notice_time, -> { joins(:venue).where("start_time >= now() + venues.notice_time * '1 hour'::interval") }

Check out this Reddit thread for more details on the above example.

Also, if you haven’t seen this before, this Railscast is an amazing resource for getting deeper into association scopes.

As always, let me know in the comments how I can help you – cheers!

6 thoughts on “Using scopes with has_many and belongs_to

  1. scope :with_pending_posts, -> { joins(:posts).where(‘posts.pending = true’) }

    why not use merge instead ?

    scope :with_pending_posts, -> { joins(:posts).merge(Post.pending) }

    The resulting sql request is identical, but you are using activerecord only instead of writing a part of sql in your where.

    1. Great thought Kevin. This definitely goes further to reduce duplication (if applicable). Not only that, its also more readable. I’ve updated the article to include this.

  2. Modifying default queries produced by has_many, has_one (and so on) is a common anti-pattern. Just a short example – you have posts and comments. In the DB you have one post and two comments assigned to it: one approved and another – not approved. Then you set “has_many :comments, -> { where(approved: true) }”. Then if somebody runs “post.comments” he/she may spend enough time trying to understand why the third comment is not getting returned.

    1. Hi Rio, thanks for your comment. I get what you’re saying but I wouldn’t go so far as to call it an anti-pattern. The key is when you’re using scopes is to name the association carefully, which can definitely be overlooked. So in your example I’d do has_many :approved_comments, -> { where(approved: true) }, class_name: 'Comment'

      Another common use I’ve seen is if you’re keeping track of deleted records with a ‘deleted_at’ column. In that case you might want scope your association so that it doesn’t return these records by default.

      1. Aye, naming is THE KEY.

        If there’s one thing I’ve learnt it’s this:

        (i) People expect certain things (mistakenly or otherwise) and

        (ii) those expectations need to be met.

        Whether it be a user, or a programmer. In calling a function, or rendering a particular view.

        The key is to make sure that the expectations are clear to all users, as much as possible. In this case, it means, having a clear and unambiguous name to the scope.

Leave a Reply

Your email address will not be published.