diff options
Diffstat (limited to 'railties/guides/source/active_record_querying.textile')
| -rw-r--r-- | railties/guides/source/active_record_querying.textile | 89 |
1 files changed, 85 insertions, 4 deletions
diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 2e1f89cb78..ad12dca7e8 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. @@ -435,10 +436,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 @@ -741,7 +758,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 @@ -1258,3 +1275,67 @@ 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> +<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> + 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> +<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) +<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. |