Rails

[Rails] Active Record Query Interface

More than 5 years have passed since last update.

http://guides.rubyonrails.org/active_record_querying.html

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
end

class Address < ActiveRecord::Base
belongs_to :client
end

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

class Role < ActiveRecord::Base
has_and_belongs_to_many :clients
end

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.

whereメソッドはwhere句と同様の機能で、文字列、配列、ハッシュをとれる。

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.

条件指定にハッシュを渡すことができるが、その場合は同一性(=)、レンジ(BETWEEN)、サブセット(IN)のみが条件として使える。

2.3.1 Equality Conditions

Client.where(locked: true)

The field name can also be a string:

Client.where('locked' => true)

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

Rangeを渡すと、BETWEENに変換される。

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

valueに配列を渡すとINに変換される。

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.

否定系のクエリはnotメソッドに引数を渡すことで、実行できる。

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:

scopeメソッドを使うことで、よく使うクエリをクラスメソッドとして定義できる。scopeではwhere,joins,includesといったメソッドを使用できる。scopeメソッド(scopeを利用して定義されたメソッド?)はActiveRecord::Relationを返すので、メソッドチェーンが可能である。

class Post < ActiveRecord::Base

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

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

Scopes are also chainable within scopes:

class Post < ActiveRecord::Base

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

このようにscopeメソッドの引数内でscopeメソッドを利用することができる(lambda内はPostクラスということ?)。

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
end

このようにpublishedメソッドは定義したPostクラスに対して呼ぶこともできるし、associationであるCategoryから間接的に呼ぶこともできる。

14.1 Passing in arguments

Your scope can take arguments:

class Post < ActiveRecord::Base

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

This may then be called using this:

Post.created_before(Time.zone.now)

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

scopeメソッドに引数を渡すような書き方もできる。この場合はclassメソッドとして書く方が好まれる(なぜ?)

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

category.posts.created_before(time)

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' }
end

User.active.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'

scopeとscopeをchainしたり、scopeとwhereをchainしたりすることができる。このとき各条件はANDでつながれる。

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

User.active.merge(User.inactive)

# => 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' }
end

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

User.active
# => 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.

default_scopeはscope、whereのどちらによっても上書きされる。

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") }
end
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.
end
end

default_scopeはself.default_scopeとして定義してもいい。

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.

Client.unscoped.load
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 {
Client.created_before(Time.zone.now)
}

scope(特にdefault_scope)をはずしたいときは、unscopedを使う。unscopedは他のscopeと一緒には使えないので、その場合はunscopedにblockを渡す。