[Rails] Active Record Query Interface

More than 5 years have passed since last update.


If you're used to using raw SQL to find database records, then you will generally find that there are better ways to carry out the same operations in Rails. Active Record insulates you from the need to use SQL in most cases.

Code examples throughout this guide will refer to one or more of the following models:

All of the following models use id as the primary key, unless specified otherwise.

class Client < ActiveRecord::Base

has_one :address
has_many :orders
has_and_belongs_to_many :roles

class Address < ActiveRecord::Base
belongs_to :client

class Order < ActiveRecord::Base
belongs_to :client, counter_cache: true

class Role < ActiveRecord::Base
has_and_belongs_to_many :clients

Active Record will perform queries on the database for you and is compatible with most database systems (MySQL, PostgreSQL and SQLite to name a few). Regardless of which database system you're using, the Active Record method format will always be the same.

2 Conditions

The where method allows you to specify conditions to limit the records returned, representing the WHERE-part of the SQL statement. Conditions can either be specified as a string, array, or hash.


2.1 Pure String Conditions

If you'd like to add conditions to your find, you could just specify them in there, just like Client.where("orders_count = '2'"). This will find all clients where the orders_count field's value is 2.

Building your own conditions as pure strings can leave you vulnerable to SQL injection exploits. For example, Client.where("first_name LIKE '%#{params[:first_name]}%'") is not safe. See the next section for the preferred way to handle conditions using an array.



2.2 Array Conditions

Now what if that number could vary, say as an argument from somewhere? The find would then take the form:

Client.where("orders_count = ?", params[:orders])

Active Record will go through the first element in the conditions value and any additional elements will replace the question marks (?) in the first element.

If you want to specify multiple conditions:

Client.where("orders_count = ? AND locked = ?", params[:orders], false)

In this example, the first question mark will be replaced with the value in params[:orders] and the second will be replaced with the SQL representation of false, which depends on the adapter.

This code is highly preferable:


Client.where("orders_count = ?", params[:orders])

to this code:

Client.where("orders_count = #{params[:orders]}")

because of argument safety. Putting the variable directly into the conditions string will pass the variable to the database as-is. This means that it will be an unescaped variable directly from a user who may have malicious intent. If you do this, you put your entire database at risk because once a user finds out they can exploit your database they can do just about anything to it. Never ever put your arguments directly inside the conditions string.

For more information on the dangers of SQL injection, see the Ruby on Rails Security Guide.


2.2.1 Placeholder Conditions

Similar to the (?) replacement style of params, you can also specify keys/values hash in your array conditions:

Client.where("created_at >= :start_date AND created_at <= :end_date",

{start_date: params[:start_date], end_date: params[:end_date]})

This makes for clearer readability if you have a large number of variable conditions.


2.3 Hash Conditions

Active Record also allows you to pass in hash conditions which can increase the readability of your conditions syntax. With hash conditions, you pass in a hash with keys of the fields you want conditionalised and the values of how you want to conditionalise them:

Only equality, range and subset checking are possible with Hash conditions.


2.3.1 Equality Conditions

Client.where(locked: true)

The field name can also be a string:

Client.where('locked' => true)


In the case of a belongs_to relationship, an association key can be used to specify the model if an Active Record object is used as the value. This method works with polymorphic relationships as well.

Post.where(author: author)

Author.joins(:posts).where(posts: {author: author})

The values cannot be symbols. For example, you cannot do Client.where(status: :active).

2.3.2 Range Conditions

Client.where(created_at: (Time.now.midnight - 1.day)..Time.now.midnight)

This will find all clients created yesterday by using a BETWEEN SQL statement:

SELECT * FROM clients WHERE (clients.created_at BETWEEN '2008-12-21 00:00:00' AND '2008-12-22 00:00:00')


This demonstrates a shorter syntax for the examples in Array Conditions

2.3.3 Subset Conditions

If you want to find records using the IN expression you can pass an array to the conditions hash:

Client.where(orders_count: [1,3,5])

This code will generate SQL like this:

SELECT * FROM clients WHERE (clients.orders_count IN (1,3,5))


2.4 NOT Conditions

NOT SQL queries can be built by where.not.

Post.where.not(author: author)
In other words, this query can be generated by calling where with no argument, then immediately chain with not passing where conditions.


14 Scopes

Scoping allows you to specify commonly-used queries which can be referenced as method calls on the association objects or models. With these scopes, you can use every method previously covered such as where, joins and includes. All scope methods will return an ActiveRecord::Relation object which will allow for further methods (such as other scopes) to be called on it.

To define a simple scope, we use the scope method inside the class, passing the query that we'd like to run when this scope is called:


class Post < ActiveRecord::Base

scope :published, -> { where(published: true) }

This is exactly the same as defining a class method, and which you use is a matter of personal preference:

class Post < ActiveRecord::Base

def self.published
where(published: true)

Scopes are also chainable within scopes:

class Post < ActiveRecord::Base

scope :published, -> { where(published: true) }
scope :published_and_commented, -> { published.where("comments_count > 0") }


To call this published scope we can call it on either the class:

Post.published # => [published posts]

Or on an association consisting of Post objects:

category = Category.first

category.posts.published # => [published posts belonging to this category]


class Category < ActiveRecord::Base

has_many :posts


14.1 Passing in arguments

Your scope can take arguments:

class Post < ActiveRecord::Base

scope :created_before, ->(time) { where("created_at < ?", time) }

This may then be called using this:


However, this is just duplicating the functionality that would be provided to you by a class method.

class Post < ActiveRecord::Base

def self.created_before(time)
where("created_at < ?", time)


Using a class method is the preferred way to accept arguments for scopes. These methods will still be accessible on the association objects:


14.2 Merging of scopes

Just like where clauses scopes are merged using AND conditions.

class User < ActiveRecord::Base

scope :active, -> { where state: 'active' }
scope :inactive, -> { where state: 'inactive' }

# => SELECT "users".* FROM "users" WHERE "users"."state" = 'active' AND "users"."state" = 'inactive'

We can mix and match scope and where conditions and the final sql will have all conditions joined with AND .

User.active.where(state: 'finished')

# => SELECT "users".* FROM "users" WHERE "users"."state" = 'active' AND "users"."state" = 'finished'


If we do want the last where clause to win then Relation#merge can be used .


# => SELECT "users".* FROM "users" WHERE "users"."state" = 'inactive'

One important caveat is that default_scope will be overridden by scope and where conditions.

class User < ActiveRecord::Base

default_scope { where state: 'pending' }
scope :active, -> { where state: 'active' }
scope :inactive, -> { where state: 'inactive' }

# => SELECT "users".* FROM "users" WHERE "users"."state" = 'pending'

# => SELECT "users".* FROM "users" WHERE "users"."state" = 'active'

User.where(state: 'inactive')
# => SELECT "users".* FROM "users" WHERE "users"."state" = 'inactive'

As you can see above the default_scope is being overridden by both scope and where conditions.


14.3 Applying a default scope

If we wish for a scope to be applied across all queries to the model we can use the default_scope method within the model itself.

class Client < ActiveRecord::Base
default_scope { where("removed_at IS NULL") }
When queries are executed on this model, the SQL query will now look something like this:

SELECT * FROM clients WHERE removed_at IS NULL
If you need to do more complex things with a default scope, you can alternatively define it as a class method:

class Client < ActiveRecord::Base
def self.default_scope
# Should return an ActiveRecord::Relation.


14.4 Removing All Scoping

If we wish to remove scoping for any reason we can use the unscoped method. This is especially useful if a default_scope is specified in the model and should not be applied for this particular query.

This method removes all scoping and will do a normal query on the table.

Note that chaining unscoped with a scope does not work. In these cases, it is recommended that you use the block form of unscoped:

Client.unscoped {