diff options
Diffstat (limited to 'railties/guides/source/active_record_querying.textile')
| -rw-r--r-- | railties/guides/source/active_record_querying.textile | 223 |
1 files changed, 209 insertions, 14 deletions
diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 2e1f89cb78..21bbc64255 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -8,6 +8,7 @@ This guide covers different ways to retrieve data from the database using Active * Use dynamic finders methods * Check for the existence of particular records * Perform various calculations on Active Record models +* Run EXPLAIN on relations endprologue. @@ -201,7 +202,7 @@ end But this approach becomes increasingly impractical as the table size increases, since +User.all.each+ instructs Active Record to fetch _the entire table_ in a single pass, build a model object per row, and then keep the entire array of model objects in memory. Indeed, if we have a large number of records, the entire collection may exceed the amount of memory available. -Rails provides two methods that address this problem by dividing records into memory-friendly batches for processing. The first method, +find_each+, retrieves a batch of records and then yields _each_ record to the block individually as a model. The second method, +find_in_batches+, retrieves a batch of records and then yields _the entire batch_ to the block as an array of models. +Rails provides two methods that address this problem by dividing records into memory-friendly batches for processing. The first method, +find_each+, retrieves a batch of records and then yields _each_ record to the block individually as a model. The second method, +find_in_batches+, retrieves a batch of records and then yields _the entire batch_ to the block as an array of models. TIP: The +find_each+ and +find_in_batches+ methods are intended for use in the batch processing of a large number of records that wouldn't fit in memory all at once. If you just need to loop over a thousand records the regular find methods are the preferred option. @@ -403,6 +404,8 @@ Or ordering by multiple fields: <ruby> Client.order("orders_count ASC, created_at DESC") +# OR +Client.order("orders_count ASC", "created_at DESC") </ruby> h3. Selecting Specific Fields @@ -435,10 +438,26 @@ ActiveModel::MissingAttributeError: missing attribute: <attribute> Where +<attribute>+ is the attribute you asked for. The +id+ method will not raise the +ActiveRecord::MissingAttributeError+, so just be careful when working with associations because they need the +id+ method to function properly. -You can also call SQL functions within the select option. For example, if you would like to only grab a single record per unique value in a certain field by using the +DISTINCT+ function you can do it like this: +If you would like to only grab a single record per unique value in a certain field, you can use +uniq+: <ruby> -Client.select("DISTINCT(name)") +Client.select(:name).uniq +</ruby> + +This would generate SQL like: + +<sql> +SELECT DISTINCT name FROM clients +</sql> + +You can also remove the uniqueness constraint: + +<ruby> +query = Client.select(:name).uniq +# => Returns unique names + +query.uniq(false) +# => Returns all names, even if there are duplicates </ruby> h3. Limit and Offset @@ -589,9 +608,33 @@ SELECT * FROM clients WHERE orders_count > 10 ORDER BY clients.id DESC This method accepts *no* arguments. +h3. Null Relation + +The +none+ method returns a chainable relation with no records. Any subsequent conditions chained to the returned relation will continue generating empty relations. This is useful in scenarios where you need a chainable response to a method or a scope that could return zero results. + +<ruby> +Post.none # returns an empty Relation and fires no queries. +</ruby> + +<ruby> +# The visible_posts method below is expected to return a Relation. +@posts = current_user.visible_posts.where(:name => params[:name]) + +def visible_posts + case role + when 'Country Manager' + Post.where(:country => country) + when 'Reviewer' + Post.published + when 'Bad User' + Post.none # => returning [] or nil breaks the caller code in this case + end +end +</ruby> + h3. Readonly Objects -Active Record provides +readonly+ method on a relation to explicitly disallow modification or deletion of any of the returned object. Any attempt to alter or destroy a readonly record will not succeed, raising an +ActiveRecord::ReadOnlyRecord+ exception. +Active Record provides +readonly+ method on a relation to explicitly disallow modification of any of the returned objects. Any attempt to alter a readonly record will not succeed, raising an +ActiveRecord::ReadOnlyRecord+ exception. <ruby> client = Client.readonly.first @@ -631,15 +674,13 @@ c2.save # Raises an ActiveRecord::StaleObjectError You're then responsible for dealing with the conflict by rescuing the exception and either rolling back, merging, or otherwise apply the business logic needed to resolve the conflict. -NOTE: You must ensure that your database schema defaults the +lock_version+ column to +0+. - This behavior can be turned off by setting <tt>ActiveRecord::Base.lock_optimistically = false</tt>. -To override the name of the +lock_version+ column, +ActiveRecord::Base+ provides a class method called +set_locking_column+: +To override the name of the +lock_version+ column, +ActiveRecord::Base+ provides a class attribute called +locking_column+: <ruby> class Client < ActiveRecord::Base - set_locking_column :lock_client_column + self.locking_column = :lock_client_column end </ruby> @@ -675,6 +716,17 @@ Item.transaction do end </ruby> +If you already have an instance of your model, you can start a transaction and acquire the lock in one go using the following code: + +<ruby> +item = Item.first +item.with_lock do + # This block is called within a transaction, + # item is already locked. + item.increment!(:views) +end +</ruby> + h3. Joining Tables Active Record provides a finder method called +joins+ for specifying +JOIN+ clauses on the resulting SQL. There are multiple ways to use the +joins+ method. @@ -741,7 +793,7 @@ SELECT categories.* FROM categories INNER JOIN posts ON posts.category_id = categories.id </sql> -Or, in English: "return a Category object for all categories with posts". Note that you will see duplicate categories if more than one post has the same category. If you want unique categories, you can use Category.joins(:post).select("distinct(categories.id)"). +Or, in English: "return a Category object for all categories with posts". Note that you will see duplicate categories if more than one post has the same category. If you want unique categories, you can use Category.joins(:post).select("distinct(categories.id)"). h5. Joining Multiple Associations @@ -937,7 +989,7 @@ If you're working with dates or times within scopes, due to how they are evaluat <ruby> class Post < ActiveRecord::Base - scope :last_week, lambda { where("created_at < ?", Time.zone.now ) } + scope :created_before_now, lambda { where("created_at < ?", Time.zone.now ) } end </ruby> @@ -949,21 +1001,21 @@ When a +lambda+ is used for a +scope+, it can take arguments: <ruby> class Post < ActiveRecord::Base - scope :1_week_before, lambda { |time| where("created_at < ?", time) + scope :created_before, lambda { |time| where("created_at < ?", time) } end </ruby> This may then be called using this: <ruby> -Post.1_week_before(Time.zone.now) +Post.created_before(Time.zone.now) </ruby> However, this is just duplicating the functionality that would be provided to you by a class method. <ruby> class Post < ActiveRecord::Base - def self.1_week_before(time) + def self.created_before(time) where("created_at < ?", time) end end @@ -972,7 +1024,7 @@ end Using a class method is the preferred way to accept arguments for scopes. These methods will still be accessible on the association objects: <ruby> -category.posts.1_week_before(time) +category.posts.created_before(time) </ruby> h4. Working with scopes @@ -1129,6 +1181,30 @@ h3. +select_all+ Client.connection.select_all("SELECT * FROM clients WHERE id = '1'") </ruby> +h3. +pluck+ + +<tt>pluck</tt> can be used to query a single column from the underlying table of a model. It accepts a column name as argument and returns an array of values of the specified column with the corresponding data type. + +<ruby> +Client.where(:active => true).pluck(:id) +# SELECT id FROM clients WHERE active = 1 + +Client.uniq.pluck(:role) +# SELECT DISTINCT role FROM clients +</ruby> + ++pluck+ makes it possible to replace code like + +<ruby> +Client.select(:id).map { |c| c.id } +</ruby> + +with + +<ruby> +Client.pluck(:id) +</ruby> + h3. Existence of Objects If you simply want to check for the existence of the object there's a method called +exists?+. This method will query the database using the same query as +find+, but instead of returning an object or collection of objects it will return either +true+ or +false+. @@ -1258,3 +1334,122 @@ Client.sum("orders_count") </ruby> For options, please see the parent section, "Calculations":#calculations. + +h3. Running EXPLAIN + +You can run EXPLAIN on the queries triggered by relations. For example, + +<ruby> +User.where(:id => 1).joins(:posts).explain +</ruby> + +may yield + +<plain> +EXPLAIN for: SELECT `users`.* FROM `users` INNER JOIN `posts` ON `posts`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 +<plus>----<plus>-------------<plus>-------<plus>-------<plus>---------------<plus>---------<plus>---------<plus>-------<plus>------<plus>-------------<plus> +| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +<plus>----<plus>-------------<plus>-------<plus>-------<plus>---------------<plus>---------<plus>---------<plus>-------<plus>------<plus>-------------<plus> +| 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +<plus>----<plus>-------------<plus>-------<plus>-------<plus>---------------<plus>---------<plus>---------<plus>-------<plus>------<plus>-------------<plus> +2 rows in set (0.00 sec) +</plain> + +under MySQL. + +Active Record performs a pretty printing that emulates the one of the database +shells. So, the same query running with the PostgreSQL adapter would yield instead + +<plain> +EXPLAIN for: SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id" WHERE "users"."id" = 1 + QUERY PLAN +------------------------------------------------------------------------------ + Nested Loop Left Join (cost=0.00..37.24 rows=8 width=0) + Join Filter: (posts.user_id = users.id) + -> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=4) + Index Cond: (id = 1) + -> Seq Scan on posts (cost=0.00..28.88 rows=8 width=4) + Filter: (posts.user_id = 1) +(6 rows) +</plain> + +Eager loading may trigger more than one query under the hood, and some queries +may need the results of previous ones. Because of that, +explain+ actually +executes the query, and then asks for the query plans. For example, + +<ruby> +User.where(:id => 1).includes(:posts).explain +</ruby> + +yields + +<plain> +EXPLAIN for: SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 +<plus>----<plus>-------------<plus>-------<plus>-------<plus>---------------<plus>---------<plus>---------<plus>-------<plus>------<plus>-------<plus> +| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +<plus>----<plus>-------------<plus>-------<plus>-------<plus>---------------<plus>---------<plus>---------<plus>-------<plus>------<plus>-------<plus> +| 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +<plus>----<plus>-------------<plus>-------<plus>-------<plus>---------------<plus>---------<plus>---------<plus>-------<plus>------<plus>-------<plus> +1 row in set (0.00 sec) + +EXPLAIN for: SELECT `posts`.* FROM `posts` WHERE `posts`.`user_id` IN (1) +<plus>----<plus>-------------<plus>-------<plus>------<plus>---------------<plus>------<plus>---------<plus>------<plus>------<plus>-------------<plus> +| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +<plus>----<plus>-------------<plus>-------<plus>------<plus>---------------<plus>------<plus>---------<plus>------<plus>------<plus>-------------<plus> +| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +<plus>----<plus>-------------<plus>-------<plus>------<plus>---------------<plus>------<plus>---------<plus>------<plus>------<plus>-------------<plus> +1 row in set (0.00 sec) +</plain> + +under MySQL. + +h4. Automatic EXPLAIN + +Active Record is able to run EXPLAIN automatically on slow queries and log its +output. This feature is controlled by the configuration parameter + +<ruby> +config.active_record.auto_explain_threshold_in_seconds +</ruby> + +If set to a number, any query exceeding those many seconds will have its EXPLAIN +automatically triggered and logged. In the case of relations, the threshold is +compared to the total time needed to fetch records. So, a relation is seen as a +unit of work, no matter whether the implementation of eager loading involves +several queries under the hood. + +A threshold of +nil+ disables automatic EXPLAINs. + +The default threshold in development mode is 0.5 seconds, and +nil+ in test and +production modes. + +INFO. Automatic EXPLAIN gets disabled if Active Record has no logger, regardless +of the value of the threshold. + +h5. Disabling Automatic EXPLAIN + +Automatic EXPLAIN can be selectively silenced with +ActiveRecord::Base.silence_auto_explain+: + +<ruby> +ActiveRecord::Base.silence_auto_explain do + # no automatic EXPLAIN is triggered here +end +</ruby> + +That may be useful for queries you know are slow but fine, like a heavyweight +report of an admin interface. + +As its name suggests, +silence_auto_explain+ only silences automatic EXPLAINs. +Explicit calls to +ActiveRecord::Relation#explain+ run. + +h4. Interpreting EXPLAIN + +Interpretation of the output of EXPLAIN is beyond the scope of this guide. The +following pointers may be helpful: + +* SQLite3: "EXPLAIN QUERY PLAN":http://www.sqlite.org/eqp.html + +* MySQL: "EXPLAIN Output Format":http://dev.mysql.com/doc/refman/5.6/en/explain-output.html + +* PostgreSQL: "Using EXPLAIN":http://www.postgresql.org/docs/current/static/using-explain.html |