From 3b5eb11664b8257d35dced58f1d65e34fa4a6c1f Mon Sep 17 00:00:00 2001 From: Xavier Noria Date: Wed, 17 Aug 2011 14:37:27 -0700 Subject: fixes generation of the AR querying guide --- railties/guides/source/active_record_querying.textile | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 8ea06d28aa..4e77a6e803 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -560,6 +560,7 @@ Client.where("orders_count > 10").order(:name).reverse_order The SQL that would be executed: + SELECT * FROM clients WHERE orders_count > 10 ORDER BY name DESC @@ -571,6 +572,7 @@ Client.where("orders_count > 10").reverse_order The SQL that would be executed: + SELECT * FROM clients WHERE orders_count > 10 ORDER BY clients.id DESC @@ -621,8 +623,6 @@ You're then responsible for dealing with the conflict by rescuing the exception NOTE: You must ensure that your database schema defaults the +lock_version+ column to +0+. -
- This behavior can be turned off by setting ActiveRecord::Base.lock_optimistically = false. To override the name of the +lock_version+ column, +ActiveRecord::Base+ provides a class method called +set_locking_column+: -- cgit v1.2.3 From 84dad446c6a23a15f67b9d558e8039891a008bff Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Andr=C3=A9s=20Mej=C3=ADa?= Date: Sat, 27 Aug 2011 23:10:25 -0500 Subject: Adding first_or_create, first_or_create!, first_or_new and first_or_build to Active Record. This let's you write things like: User.where(:first_name => "Scarlett").first_or_create!(:last_name => "Johansson", :hot => true) Related to #2420. --- .../guides/source/active_record_querying.textile | 75 ++++++++++++++++++++-- 1 file changed, 68 insertions(+), 7 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 4e77a6e803..37874c2ea1 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -1018,23 +1018,84 @@ If you want to find both by name and locked, you can chain these finders togethe WARNING: Up to and including Rails 3.1, when the number of arguments passed to a dynamic finder method is lesser than the number of fields, say Client.find_by_name_and_locked("Ryan"), the behavior is to pass +nil+ as the missing argument. This is *unintentional* and this behavior will be changed in Rails 3.2 to throw an +ArgumentError+. -There's another set of dynamic finders that let you find or create/initialize objects if they aren't found. These work in a similar fashion to the other finders and can be used like +find_or_create_by_first_name(params[:first_name])+. Using this will first perform a find and then create if the find returns +nil+. The SQL looks like this for +Client.find_or_create_by_first_name("Ryan")+: +h3. Find or build a new object + +It's common that you need to find a record or create it if it doesn't exist. You can do that with the +first_or_create+ and +first_or_create!+ methods. + +h4. +first_or_create+ + +The +first_or_create+ method checks whether +first+ returns +nil+ or not. If it does return +nil+, then +create+ is called. This is very powerful when coupled with the +where+ method. Let's see an example. + +Suppose you want to find a client named 'Andy', and if there's none, create one and additionally set his +locked+ attribute to false. You can do so by running: + + +Client.where(:first_name => 'Andy').first_or_create(:locked => false) +# => + + +The SQL generated by this method looks like this: -SELECT * FROM clients WHERE (clients.first_name = 'Ryan') LIMIT 1 +SELECT * FROM clients WHERE (clients.first_name = 'Andy') LIMIT 1 BEGIN -INSERT INTO clients (first_name, updated_at, created_at, orders_count, locked) - VALUES('Ryan', '2008-09-28 15:39:12', '2008-09-28 15:39:12', 0, '0') +INSERT INTO clients (created_at, first_name, locked, orders_count, updated_at) VALUES ('2011-08-30 05:22:57', 'Andy', 0, NULL, '2011-08-30 05:22:57') COMMIT -+find_or_create+'s sibling, +find_or_initialize+, will find an object and if it does not exist will act similarly to calling +new+ with the arguments you passed in. For example: ++first_or_create+ returns either the record that already existed or the new record. In our case, we didn't already have a client named Andy so the record was created an returned. + +The new record might not be saved to the database; that depends on whether validations passed or not (just like +create+). + +It's also worth noting that +first_or_create+ takes into account the arguments of the +where+ method. In the example above we didn't explicitly pass a +:first_name => 'Andy'+ argument to +first_or_create+. However, that was used when creating the new record because it was already passed before to the +where+ method. + +NOTE: On previous versions of Rails you could do a similar thing with the +find_or_create_by+ method. Following our example, you could also run something like +Client.find_or_create_by_first_name(:first_name => "Andy", :locked => false)+. This method still works, but it's encouraged to use +first_or_create+ because it's more explicit on what arguments are used to _find_ the record and what arguments are used to _create_ it, resulting in less confusion overall. + +h4. +first_or_create!+ + +You can also use +first_or_create!+ to raise an exception if the new record is invalid. Validations are not covered on this guide, but let's assume for a moment that you temporarily add + + + validates :orders_count, :presence => true + + +to your +Client+ model. If you try to create a new +Client+ without passing an +orders_count+, the record will be invalid and an exception will be raised: + + +Client.where(:first_name => 'Andy').first_or_create!(:locked => false) +# => ActiveRecord::RecordInvalid: Validation failed: Orders count can't be blank + + +NOTE: Be sure to check the extensive *Active Record Validations and Callbacks Guide* for more information about validations. + +h4. +first_or_new+ + +The +first_or_new+ method will work just like +first_or_create+ but it will not call +create+ but the +new+. This means that a new model instance will be created in memory but won't be saved to the database. Continuing with the +first_or_create+ example, we now want the client named 'Nick': + + +nick = Client.where(:first_name => 'Nick').first_or_new(:locked => false) +# => + +nick.persisted? +# => false + +nick.new_record? +# => true + + +Because the object is not yet stored in the database, the SQL generated looks like this: + + +SELECT * FROM clients WHERE (clients.first_name = 'Nick') LIMIT 1 + + +When you want to save it to the database, just call +save+: -client = Client.find_or_initialize_by_first_name('Ryan') +nick.save +# => true -will either assign an existing client object with the name "Ryan" to the client local variable, or initialize a new object similar to calling +Client.new(:first_name => 'Ryan')+. From here, you can modify other fields in client by calling the attribute setters on it: +client.locked = true+ and when you want to write it to the database just call +save+ on it. +Just like you can use *+build+* instead of *+new+*, you can use *+first_or_build+* instead of *+first_or_new+*. h3. Finding by SQL -- cgit v1.2.3 From ee822f257e2f3f3b571772f30ae3a8edd69538c3 Mon Sep 17 00:00:00 2001 From: Nicolas Hock Isaza Date: Tue, 30 Aug 2011 15:40:09 -0500 Subject: Adding `first_or_new` documentation to the AR Querying guide. --- railties/guides/source/active_record_querying.textile | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 37874c2ea1..54ce7a25ab 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -1069,7 +1069,7 @@ NOTE: Be sure to check the extensive *Active Record Validations and Callbacks Gu h4. +first_or_new+ -The +first_or_new+ method will work just like +first_or_create+ but it will not call +create+ but the +new+. This means that a new model instance will be created in memory but won't be saved to the database. Continuing with the +first_or_create+ example, we now want the client named 'Nick': +The +first_or_new+ method will work just like +first_or_create+ but it will not call +create+ but +new+. This means that a new model instance will be created in memory but won't be saved to the database. Continuing with the +first_or_create+ example, we now want the client named 'Nick': nick = Client.where(:first_name => 'Nick').first_or_new(:locked => false) -- cgit v1.2.3 From f83f169b85eea4f580ae95609506fbb3cc5b8ccb Mon Sep 17 00:00:00 2001 From: Vijay Dev Date: Sat, 3 Sep 2011 01:40:00 +0530 Subject: some of the changes for validation earlier reverted from d20281a --- railties/guides/source/active_record_querying.textile | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 4e77a6e803..95a7bfebc3 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -132,7 +132,7 @@ SELECT * FROM clients ORDER BY clients.id DESC LIMIT 1 Model.last returns +nil+ if no matching record is found. No exception will be raised. -h5. +first!+ +h5(#first_1). +first!+ Model.first! finds the first record. For example: @@ -149,7 +149,7 @@ SELECT * FROM clients LIMIT 1 Model.first! raises +RecordNotFound+ if no matching record is found. -h5. +last!+ +h5(#last_1). +last!+ Model.last! finds the last record. For example: -- cgit v1.2.3 From 5e1285dfb3148f89ae644bdf4522115ae4c8144f Mon Sep 17 00:00:00 2001 From: Vijay Dev Date: Wed, 14 Sep 2011 23:05:29 +0530 Subject: change first_or_new to first_or_initialize as per 11870117, and some edits --- .../guides/source/active_record_querying.textile | 22 ++++++++++++---------- 1 file changed, 12 insertions(+), 10 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 7a853db813..ac8c15f60d 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -1042,20 +1042,26 @@ INSERT INTO clients (created_at, first_name, locked, orders_count, updated_at) V COMMIT -+first_or_create+ returns either the record that already existed or the new record. In our case, we didn't already have a client named Andy so the record was created an returned. ++first_or_create+ returns either the record that already exists or the new record. In our case, we didn't already have a client named Andy so the record is created and returned. The new record might not be saved to the database; that depends on whether validations passed or not (just like +create+). It's also worth noting that +first_or_create+ takes into account the arguments of the +where+ method. In the example above we didn't explicitly pass a +:first_name => 'Andy'+ argument to +first_or_create+. However, that was used when creating the new record because it was already passed before to the +where+ method. -NOTE: On previous versions of Rails you could do a similar thing with the +find_or_create_by+ method. Following our example, you could also run something like +Client.find_or_create_by_first_name(:first_name => "Andy", :locked => false)+. This method still works, but it's encouraged to use +first_or_create+ because it's more explicit on what arguments are used to _find_ the record and what arguments are used to _create_ it, resulting in less confusion overall. +You can do the same with the +find_or_create_by+ method: + + +Client.find_or_create_by_first_name(:first_name => "Andy", :locked => false) + + +This method still works, but it's encouraged to use +first_or_create+ because it's more explicit on which arguments are used to _find_ the record and which are used to _create_, resulting in less confusion overall. h4. +first_or_create!+ You can also use +first_or_create!+ to raise an exception if the new record is invalid. Validations are not covered on this guide, but let's assume for a moment that you temporarily add - validates :orders_count, :presence => true +validates :orders_count, :presence => true to your +Client+ model. If you try to create a new +Client+ without passing an +orders_count+, the record will be invalid and an exception will be raised: @@ -1065,14 +1071,12 @@ Client.where(:first_name => 'Andy').first_or_create!(:locked => false) # => ActiveRecord::RecordInvalid: Validation failed: Orders count can't be blank -NOTE: Be sure to check the extensive *Active Record Validations and Callbacks Guide* for more information about validations. - -h4. +first_or_new+ +h4. +first_or_initialize+ -The +first_or_new+ method will work just like +first_or_create+ but it will not call +create+ but +new+. This means that a new model instance will be created in memory but won't be saved to the database. Continuing with the +first_or_create+ example, we now want the client named 'Nick': +The +first_or_initialize+ method will work just like +first_or_create+ but it will not call +create+ but +new+. This means that a new model instance will be created in memory but won't be saved to the database. Continuing with the +first_or_create+ example, we now want the client named 'Nick': -nick = Client.where(:first_name => 'Nick').first_or_new(:locked => false) +nick = Client.where(:first_name => 'Nick').first_or_initialize(:locked => false) # => nick.persisted? @@ -1095,8 +1099,6 @@ nick.save # => true -Just like you can use *+build+* instead of *+new+*, you can use *+first_or_build+* instead of *+first_or_new+*. - h3. Finding by SQL If you'd like to use your own SQL to find records in a table you can use +find_by_sql+. The +find_by_sql+ method will return an array of objects even if the underlying query returns just a single record. For example you could run this query: -- cgit v1.2.3 From 9980f46ca2d250d1d3e2fac84c5dc9ca6cbab1ea Mon Sep 17 00:00:00 2001 From: Vijay Dev Date: Thu, 15 Sep 2011 00:13:29 +0530 Subject: No more changelogs inside guides --- railties/guides/source/active_record_querying.textile | 9 --------- 1 file changed, 9 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index ac8c15f60d..96f91cfef6 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -1248,12 +1248,3 @@ Client.sum("orders_count") For options, please see the parent section, "Calculations":#calculations. - -h3. Changelog - -* June 26 2011: Added documentation for the +scoped+, +unscoped+ and +default+ methods. "Ryan Bigg":credits.html#radar -* December 23 2010: Add documentation for the +scope+ method. "Ryan Bigg":credits.html#radar -* April 7, 2010: Fixed document to validate XHTML 1.0 Strict. "Jaime Iniesta":http://jaimeiniesta.com -* February 3, 2010: Update to Rails 3 by "James Miller":credits.html#bensie -* February 7, 2009: Second version by "Pratik":credits.html#lifo -* December 29 2008: Initial version by "Ryan Bigg":credits.html#radar -- cgit v1.2.3 From 0678a5bb18b7045054a03bbe2ca0434486a7d404 Mon Sep 17 00:00:00 2001 From: Michael Hutchinson Date: Tue, 27 Sep 2011 18:11:01 -0700 Subject: Corrected a typo. --- railties/guides/source/active_record_querying.textile | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 96f91cfef6..b1acdd189a 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -616,7 +616,7 @@ c1.first_name = "Michael" c1.save c2.name = "should fail" -c2.save # Raises a ActiveRecord::StaleObjectError +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. -- cgit v1.2.3 From cde529448bfb71a9acbbcc40622688f4511aecd5 Mon Sep 17 00:00:00 2001 From: Manuel Menezes de Sequeira Date: Wed, 5 Oct 2011 22:52:50 +0100 Subject: Some small corrections and improvements to the text. --- .../guides/source/active_record_querying.textile | 62 +++++++++++----------- 1 file changed, 32 insertions(+), 30 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index b1acdd189a..6b6f4d4983 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -13,7 +13,7 @@ endprologue. WARNING. This Guide is based on Rails 3.0. Some of the code shown here will not work in other versions of Rails. -If you're used to using raw SQL to find database records then, generally, you will 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. +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: @@ -69,16 +69,16 @@ The methods are: All of the above methods return an instance of ActiveRecord::Relation. -Primary operation of Model.find(options) can be summarized as: +The primary operation of Model.find(options) can be summarized as: * Convert the supplied options to an equivalent SQL query. * Fire the SQL query and retrieve the corresponding results from the database. * Instantiate the equivalent Ruby object of the appropriate model for every resulting row. -* Run +after_find+ callbacks if any. +* Run +after_find+ callbacks, if any. h4. Retrieving a Single Object -Active Record lets you retrieve a single object using five different ways. +Active Record provides five different ways of retrieving a single object. h5. Using a Primary Key @@ -87,10 +87,10 @@ Using Model.find(primary_key), you can retrieve the object correspondin # Find the client with primary key (id) 10. client = Client.find(10) -=> # "Ryan"> +=> # -SQL equivalent of the above is: +The SQL equivalent of the above is: SELECT * FROM clients WHERE (clients.id = 10) @@ -100,14 +100,14 @@ SELECT * FROM clients WHERE (clients.id = 10) h5. +first+ -Model.first finds the first record matched by the supplied options. For example: +Model.first finds the first record matched by the supplied options, if any. For example: client = Client.first => # -SQL equivalent of the above is: +The SQL equivalent of the above is: SELECT * FROM clients LIMIT 1 @@ -124,7 +124,7 @@ client = Client.last => # -SQL equivalent of the above is: +The SQL equivalent of the above is: SELECT * FROM clients ORDER BY clients.id DESC LIMIT 1 @@ -141,7 +141,7 @@ client = Client.first! => # -SQL equivalent of the above is: +The SQL equivalent of the above is: SELECT * FROM clients LIMIT 1 @@ -158,7 +158,7 @@ client = Client.last! => # -SQL equivalent of the above is: +The SQL equivalent of the above is: SELECT * FROM clients ORDER BY clients.id DESC LIMIT 1 @@ -174,11 +174,11 @@ h5. Using Multiple Primary Keys # Find the clients with primary keys 1 and 10. -client = Client.find(1, 10) # Or even Client.find([1, 10]) -=> [# "Lifo">, # "Ryan">] +client = Client.find([1, 10]) # Or even Client.find(1, 10) +=> [#, #] -SQL equivalent of the above is: +The SQL equivalent of the above is: SELECT * FROM clients WHERE (clients.id IN (1,10)) @@ -190,7 +190,7 @@ h4. Retrieving Multiple Objects in Batches Sometimes you need to iterate over a large set of records. For example to send a newsletter to all users, to export some data, etc. -The following may seem very straight forward at first: +The following may seem very straightforward, at first: # Very inefficient when users table has thousands of rows. @@ -199,9 +199,9 @@ User.all.each do |user| end -But if the total number of rows in the table is very large, the above approach may vary from being under performant to just plain impossible. +But if the total number of rows in the table is very large, the above approach may vary from being underperforming to being plain impossible. -This is because +User.all.each+ makes Active Record fetch _the entire table_, build a model object per row, and keep the entire array in the memory. Sometimes that is just too many objects and demands too much memory. +This is because +User.all.each+ makes Active Record fetch _the entire table_, build a model object per row, and keep the entire array of model objects in memory. Sometimes that is just too many objects and requires too much memory. h5. +find_each+ @@ -215,9 +215,9 @@ end *Configuring the batch size* -Behind the scenes +find_each+ fetches rows in batches of +1000+ and yields them one by one. The size of the underlying batches is configurable via the +:batch_size+ option. +Behind the scenes, +find_each+ fetches rows in batches of 1000 and yields them one by one. The size of the underlying batches is configurable via the +:batch_size+ option. -To fetch +User+ records in batch size of +5000+: +To fetch +User+ records in batches of 5000, we can use: User.find_each(:batch_size => 5000) do |user| @@ -227,9 +227,9 @@ end *Starting batch find from a specific primary key* -Records are fetched in ascending order on the primary key, which must be an integer. The +:start+ option allows you to configure the first ID of the sequence if the lowest is not the one you need. This may be useful for example to be able to resume an interrupted batch process if it saves the last processed ID as a checkpoint. +Records are fetched in ascending order of the primary key, which must be an integer. The +:start+ option allows you to configure the first ID of the sequence whenever the lowest ID is not the one you need. This may be useful, for example, to be able to resume an interrupted batch process, provided it saves the last processed ID as a checkpoint. -To send newsletters only to users with the primary key starting from +2000+: +To send newsletters only to users with the primary key starting from 2000, we can use: User.find_each(:batch_size => 5000, :start => 2000) do |user| @@ -252,7 +252,9 @@ Invoice.find_in_batches(:include => :invoice_lines) do |invoices| end -The above will yield the supplied block with +1000+ invoices every time. +The above will each time yield to the supplied block an arrays of 1000 invoices (or the remaining invoices, if less than 1000). + +NOTE: The +:include+ option allows you to name associations that should be loaded alongside with the models. h3. Conditions @@ -911,14 +913,14 @@ end To call this +published+ scope we can call it on either the class: -Post.published => [published posts] +Post.published # => [published posts] Or on an association consisting of +Post+ objects: category = Category.first -category.posts.published => [published posts belonging to this category] +category.posts.published # => [published posts belonging to this category] h4. Working with times @@ -1030,7 +1032,7 @@ Suppose you want to find a client named 'Andy', and if there's none, create one Client.where(:first_name => 'Andy').first_or_create(:locked => false) -# => +=> # The SQL generated by this method looks like this: @@ -1068,7 +1070,7 @@ to your +Client+ model. If you try to create a new +Client+ without passing an + Client.where(:first_name => 'Andy').first_or_create!(:locked => false) -# => ActiveRecord::RecordInvalid: Validation failed: Orders count can't be blank +=> ActiveRecord::RecordInvalid: Validation failed: Orders count can't be blank h4. +first_or_initialize+ @@ -1077,13 +1079,13 @@ The +first_or_initialize+ method will work just like +first_or_create+ but it wi nick = Client.where(:first_name => 'Nick').first_or_initialize(:locked => false) -# => +=> nick.persisted? -# => false +=> false nick.new_record? -# => true +=> true Because the object is not yet stored in the database, the SQL generated looks like this: @@ -1096,7 +1098,7 @@ When you want to save it to the database, just call +save+: nick.save -# => true +=> true h3. Finding by SQL -- cgit v1.2.3 From 29bf193cadae8c0b01f565caed75eb285ba8c958 Mon Sep 17 00:00:00 2001 From: Manuel Menezes de Sequeira Date: Thu, 6 Oct 2011 21:29:58 +0100 Subject: Undid previous change which violated the convention regarding output (use "# =>") used in these guides. Corrected typo in previous correction. (Thanks for pointing this out, vijaydev.) --- .../guides/source/active_record_querying.textile | 26 +++++++++++----------- 1 file changed, 13 insertions(+), 13 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 6b6f4d4983..81d73c4ccc 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -87,7 +87,7 @@ Using Model.find(primary_key), you can retrieve the object correspondin # Find the client with primary key (id) 10. client = Client.find(10) -=> # +# => # The SQL equivalent of the above is: @@ -104,7 +104,7 @@ h5. +first+ client = Client.first -=> # +# => # The SQL equivalent of the above is: @@ -121,7 +121,7 @@ h5. +last+ client = Client.last -=> # +# => # The SQL equivalent of the above is: @@ -138,7 +138,7 @@ h5(#first_1). +first!+ client = Client.first! -=> # +# => # The SQL equivalent of the above is: @@ -155,7 +155,7 @@ h5(#last_1). +last!+ client = Client.last! -=> # +# => # The SQL equivalent of the above is: @@ -175,7 +175,7 @@ h5. Using Multiple Primary Keys # Find the clients with primary keys 1 and 10. client = Client.find([1, 10]) # Or even Client.find(1, 10) -=> [#, #] +# => [#, #] The SQL equivalent of the above is: @@ -252,7 +252,7 @@ Invoice.find_in_batches(:include => :invoice_lines) do |invoices| end -The above will each time yield to the supplied block an arrays of 1000 invoices (or the remaining invoices, if less than 1000). +The above will each time yield to the supplied block an array of 1000 invoices (or the remaining invoices, if less than 1000). NOTE: The +:include+ option allows you to name associations that should be loaded alongside with the models. @@ -1032,7 +1032,7 @@ Suppose you want to find a client named 'Andy', and if there's none, create one Client.where(:first_name => 'Andy').first_or_create(:locked => false) -=> # +# => # The SQL generated by this method looks like this: @@ -1070,7 +1070,7 @@ to your +Client+ model. If you try to create a new +Client+ without passing an + Client.where(:first_name => 'Andy').first_or_create!(:locked => false) -=> ActiveRecord::RecordInvalid: Validation failed: Orders count can't be blank +# => ActiveRecord::RecordInvalid: Validation failed: Orders count can't be blank h4. +first_or_initialize+ @@ -1079,13 +1079,13 @@ The +first_or_initialize+ method will work just like +first_or_create+ but it wi nick = Client.where(:first_name => 'Nick').first_or_initialize(:locked => false) -=> +# => nick.persisted? -=> false +# => false nick.new_record? -=> true +# => true Because the object is not yet stored in the database, the SQL generated looks like this: @@ -1098,7 +1098,7 @@ When you want to save it to the database, just call +save+: nick.save -=> true +# => true h3. Finding by SQL -- cgit v1.2.3 From 3ca269674f66558f6ced4d956ecae4959dacd596 Mon Sep 17 00:00:00 2001 From: mhutchin Date: Tue, 11 Oct 2011 06:27:01 -0700 Subject: Heavy copy editing of the find_each and find_in_batches section --- .../guides/source/active_record_querying.textile | 60 ++++++++++++---------- 1 file changed, 34 insertions(+), 26 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 81d73c4ccc..6ac9197f44 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -82,7 +82,7 @@ Active Record provides five different ways of retrieving a single object. h5. Using a Primary Key -Using Model.find(primary_key), you can retrieve the object corresponding to the supplied _primary key_ and matching the supplied options (if any). For example: +Using Model.find(primary_key), you can retrieve the object corresponding to the specified _primary key_ that matches any supplied options. For example: # Find the client with primary key (id) 10. @@ -170,7 +170,7 @@ h4. Retrieving Multiple Objects h5. Using Multiple Primary Keys -Model.find(array_of_primary_key) also accepts an array of _primary keys_. An array of all the matching records for the supplied _primary keys_ is returned. For example: +Model.find(array_of_primary_key) accepts an array of _primary keys_, returning an array containing all of the matching records for the supplied _primary keys_. For example: # Find the clients with primary keys 1 and 10. @@ -188,24 +188,26 @@ WARNING: Model.find(array_of_primary_key) will raise an +ActiveRecord:: h4. Retrieving Multiple Objects in Batches -Sometimes you need to iterate over a large set of records. For example to send a newsletter to all users, to export some data, etc. +We often need to iterate over a large set of records, as when we send a newsletter to a large set of users, or when we export data. -The following may seem very straightforward, at first: +This may appear straightforward: -# Very inefficient when users table has thousands of rows. +# This is very inefficient when the users table has thousands of rows. User.all.each do |user| NewsLetter.weekly_deliver(user) end -But if the total number of rows in the table is very large, the above approach may vary from being underperforming to being plain impossible. +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. -This is because +User.all.each+ makes Active Record fetch _the entire table_, build a model object per row, and keep the entire array of model objects in memory. Sometimes that is just too many objects and requires too much memory. +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. h5. +find_each+ -To efficiently iterate over a large table, Active Record provides a batch finder method called +find_each+: +The +find_each+ method retrieves a batch of records and then yields _each_ record to the block individually as a model. In the following example, +find_each+ will retrieve 1000 records (the current default for both +find_each+ and +find_in_batches+) and then yield each record individually to the block as a model. This process is repeated until all of the records have been processed: User.find_each do |user| @@ -213,11 +215,15 @@ User.find_each do |user| end -*Configuring the batch size* +h6. Options for +find_each+ + +The +find_each+ method accepts most of the options allowed by the regular +find+ method, except for +:order+ and +:limit+, which are reserved for internal use by +find_each+. -Behind the scenes, +find_each+ fetches rows in batches of 1000 and yields them one by one. The size of the underlying batches is configurable via the +:batch_size+ option. +Two additional options, +:batch_size+ and +:start+, are available as well. -To fetch +User+ records in batches of 5000, we can use: +*+:batch_size+* + +The +:batch_size+ option allows you to specify the number of records to be retrieved in each batch, before being passed individually to the block. For example, to retrieve records in batches of 5000: User.find_each(:batch_size => 5000) do |user| @@ -225,37 +231,39 @@ User.find_each(:batch_size => 5000) do |user| end -*Starting batch find from a specific primary key* +*+:start+* -Records are fetched in ascending order of the primary key, which must be an integer. The +:start+ option allows you to configure the first ID of the sequence whenever the lowest ID is not the one you need. This may be useful, for example, to be able to resume an interrupted batch process, provided it saves the last processed ID as a checkpoint. +By default, records are fetched in ascending order of the primary key, which must be an integer. The +:start+ option allows you to configure the first ID of the sequence whenever the lowest ID is not the one you need. This would be useful, for example, if you wanted to resume an interrupted batch process, provided you saved the last processed ID as a checkpoint. -To send newsletters only to users with the primary key starting from 2000, we can use: +For example, to send newsletters only to users with the primary key starting from 2000, and to retrieve them in batches of 5000: -User.find_each(:batch_size => 5000, :start => 2000) do |user| +User.find_each(:start => 2000, :batch_size => 5000) do |user| NewsLetter.weekly_deliver(user) end -*Additional options* +Another example would be if you wanted multiple workers handling the same processing queue. You could have each worker handle 10000 records by setting the appropriate :start option on each worker. -+find_each+ accepts the same options as the regular +find+ method. However, +:order+ and +:limit+ are needed internally and hence not allowed to be passed explicitly. +NOTE: The +:include+ option allows you to name associations that should be loaded alongside with the models. h5. +find_in_batches+ -You can also work by chunks instead of row by row using +find_in_batches+. This method is analogous to +find_each+, but it yields arrays of models instead: +The +find_in_batches+ method is similar to +find_each+, since both retrieve batches of records. The difference is that +find_in_batches+ yields _batches_ to the block as an array of models, instead of individually. The following example will yield to the supplied block an array of up to 1000 invoices at a time, with the final block containing any remaining invoices: -# Works in chunks of 1000 invoices at a time. +# Give add_invoices an array of 1000 invoices at a time Invoice.find_in_batches(:include => :invoice_lines) do |invoices| export.add_invoices(invoices) end -The above will each time yield to the supplied block an array of 1000 invoices (or the remaining invoices, if less than 1000). - NOTE: The +:include+ option allows you to name associations that should be loaded alongside with the models. +h6. Options for +find_in_batches+ + +The +find_in_batches+ method accepts the same +:batch_size+ and +:start+ options as +find_each+, as well as most of the options allowed by the regular +find+ method, except for +:order+ and +:limit+, which are reserved for internal use by +find_in_batches+. + h3. 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. @@ -268,7 +276,7 @@ WARNING: Building your own conditions as pure strings can leave you vulnerable t h4. Array Conditions -Now what if that number could vary, say as an argument from somewhere? The find then becomes something like: +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]) @@ -276,7 +284,7 @@ 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. -Or if you want to specify two conditions, you can do it like: +If you want to specify multiple conditions: Client.where("orders_count = ? AND locked = ?", params[:orders], false) @@ -284,19 +292,19 @@ 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. -The reason for doing code like: +This code is highly preferable: Client.where("orders_count = ?", params[:orders]) -instead of: +to this code: Client.where("orders_count = #{params[:orders]}") -is 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 he or she can exploit your database they can do just about anything to it. Never ever put your arguments directly inside the conditions string. +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 he or she can exploit your database they can do just about anything to it. Never ever put your arguments directly inside the conditions string. TIP: For more information on the dangers of SQL injection, see the "Ruby on Rails Security Guide":security.html#sql-injection. -- cgit v1.2.3 From 532e6d59b50b241d51d2a423bab5962d9cf7b0d4 Mon Sep 17 00:00:00 2001 From: Craig Monson Date: Wed, 12 Oct 2011 10:34:27 -0400 Subject: quoting 'and' to make it more distinct. --- railties/guides/source/active_record_querying.textile | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 6ac9197f44..2e1f89cb78 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -1024,7 +1024,7 @@ You can also use +find_last_by_*+ methods which will find the last record matchi You can specify an exclamation point (!) on the end of the dynamic finders to get them to raise an +ActiveRecord::RecordNotFound+ error if they do not return any records, like +Client.find_by_name!("Ryan")+ -If you want to find both by name and locked, you can chain these finders together by simply typing +and+ between the fields. For example, +Client.find_by_first_name_and_locked("Ryan", true)+. +If you want to find both by name and locked, you can chain these finders together by simply typing "+and+" between the fields. For example, +Client.find_by_first_name_and_locked("Ryan", true)+. WARNING: Up to and including Rails 3.1, when the number of arguments passed to a dynamic finder method is lesser than the number of fields, say Client.find_by_name_and_locked("Ryan"), the behavior is to pass +nil+ as the missing argument. This is *unintentional* and this behavior will be changed in Rails 3.2 to throw an +ArgumentError+. -- cgit v1.2.3 From 562583c7667f508493ab8c5b1a4215087fafd22d Mon Sep 17 00:00:00 2001 From: Jon Leighton Date: Fri, 4 Nov 2011 16:10:18 +0000 Subject: Add ActiveRecord::Relation#uniq for toggling DISTINCT in the SQL query --- .../guides/source/active_record_querying.textile | 24 ++++++++++++++++++---- 1 file changed, 20 insertions(+), 4 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 2e1f89cb78..0ad2644095 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -201,7 +201,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 +435,26 @@ ActiveModel::MissingAttributeError: missing 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+: -Client.select("DISTINCT(name)") +Client.select(:name).uniq + + +This would generate SQL like: + + +SELECT DISTINCT name FROM clients + + +You can also remove the uniqueness constraint: + + +query = Client.select(:name).uniq +# => Returns unique names + +query.uniq(false) +# => Returns all names, even if there are duplicates h3. Limit and Offset @@ -741,7 +757,7 @@ SELECT categories.* FROM categories INNER JOIN posts ON posts.category_id = 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)"). +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 -- cgit v1.2.3 From e7b7b4412380e7ce2d8e6ae402cb7fe02d7666b8 Mon Sep 17 00:00:00 2001 From: Xavier Noria Date: Tue, 20 Sep 2011 10:50:08 -0700 Subject: implements AR::Relation#explain This is a first implementation, EXPLAIN is highly dependent on the database and I have made some compromises. On one hand, the method allows you to run the most common EXPLAIN and that's it. If you want EXPLAIN ANALYZE in PostgreSQL you need to do it by hand. On the other hand, I've tried to construct a string as close as possible to the ones built by the respective shells. The rationale is that IMO the user should feel at home with the output and recognize it at first sight. Per database. I don't know whether this implementation is going to work well. Let's see whether people like it. --- .../guides/source/active_record_querying.textile | 65 ++++++++++++++++++++++ 1 file changed, 65 insertions(+) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 0ad2644095..a132d85ef9 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. @@ -1274,3 +1275,67 @@ Client.sum("orders_count") For options, please see the parent section, "Calculations":#calculations. + +h3. Running EXPLAIN + +You can run EXPLAIN on the queries triggered by relations. For example, + + +User.where(:id => 1).joins(:posts).explain + + +may yield + + ++----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ +| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ +| 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 1 | Using where | ++----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ +2 rows in set (0.00 sec) + + +under MySQL. + +Active Record performs a pretty printing that emulates the one of the database +shells. So, the same query running with the PostreSQL adapter would yield instead + + + 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) + + +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, + + +User.where(:id => 1).includes(:posts).explain + + +yields + + ++----+-------------+-------+-------+---------------+---------+---------+-------+------+------- +| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++----+-------------+-------+-------+---------------+---------+---------+-------+------+------- +| 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | ++----+-------------+-------+-------+---------------+---------+---------+-------+------+------- +1 row in set (0.00 sec) ++----+-------------+-------+------+---------------+------+---------+------+------+------------- +| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++----+-------------+-------+------+---------------+------+---------+------+------+------------- +| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 1 | Using where | ++----+-------------+-------+------+---------------+------+---------+------+------+------------- +1 row in set (0.00 sec) + + +under MySQL. -- cgit v1.2.3 From 8750c9a1cfb055fb45a798fee952953b6db829ad Mon Sep 17 00:00:00 2001 From: Vijay Dev Date: Sun, 6 Nov 2011 18:58:55 +0530 Subject: fix markups for plus in AR guide --- .../guides/source/active_record_querying.textile | 20 ++++++++++---------- 1 file changed, 10 insertions(+), 10 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index a132d85ef9..8526d8511b 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -1287,19 +1287,19 @@ User.where(:id => 1).joins(:posts).explain may yield -+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ +------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | -+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ +------------------------------------------------------------------------------------------ | 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 1 | Using where | -+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ +------------------------------------------------------------------------------------------ 2 rows in set (0.00 sec) under MySQL. Active Record performs a pretty printing that emulates the one of the database -shells. So, the same query running with the PostreSQL adapter would yield instead +shells. So, the same query running with the PostgreSQL adapter would yield instead QUERY PLAN @@ -1324,17 +1324,17 @@ User.where(:id => 1).includes(:posts).explain yields -+----+-------------+-------+-------+---------------+---------+---------+-------+------+------- +------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | -+----+-------------+-------+-------+---------------+---------+---------+-------+------+------- +------------------------------------------------------------------------------------ | 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | -+----+-------------+-------+-------+---------------+---------+---------+-------+------+------- +------------------------------------------------------------------------------------ 1 row in set (0.00 sec) -+----+-------------+-------+------+---------------+------+---------+------+------+------------- +------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | -+----+-------------+-------+------+---------------+------+---------+------+------+------------- +------------------------------------------------------------------------------------- | 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 1 | Using where | -+----+-------------+-------+------+---------------+------+---------+------+------+------------- +------------------------------------------------------------------------------------- 1 row in set (0.00 sec) -- cgit v1.2.3 From 17ecdd388c70f7faf002ef21be6a674b4c0df7ca Mon Sep 17 00:00:00 2001 From: Xavier Noria Date: Mon, 7 Nov 2011 02:01:37 -0800 Subject: adds trailing +s to the output of EXPLAIN for MySQL --- railties/guides/source/active_record_querying.textile | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index a132d85ef9..ad623ae434 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -1324,17 +1324,17 @@ User.where(:id => 1).includes(:posts).explain yields -+----+-------------+-------+-------+---------------+---------+---------+-------+------+------- ++----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | -+----+-------------+-------+-------+---------------+---------+---------+-------+------+------- ++----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | -+----+-------------+-------+-------+---------------+---------+---------+-------+------+------- ++----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec) -+----+-------------+-------+------+---------------+------+---------+------+------+------------- ++----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | -+----+-------------+-------+------+---------------+------+---------+------+------+------------- ++----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 1 | Using where | -+----+-------------+-------+------+---------------+------+---------+------+------+------------- ++----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) -- cgit v1.2.3 From 1be9830d4d99e2bf56f1cadf74b843f22d66da35 Mon Sep 17 00:00:00 2001 From: Xavier Noria Date: Fri, 25 Nov 2011 14:29:34 -0800 Subject: add the query to AR::Relation#explain output Rationale: this is more readable if serveral queries are involved in one call. Also, it will be possible to let AR log EXPLAINs automatically in production mode, where queries are not even around. --- railties/guides/source/active_record_querying.textile | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index ad12dca7e8..0f1f6eba4c 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -1287,6 +1287,7 @@ User.where(:id => 1).joins(:posts).explain may yield +EXPLAIN for: SELECT `users`.* FROM `users` INNER JOIN `posts` ON `posts`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 ------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------ @@ -1302,6 +1303,7 @@ 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 +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) @@ -1324,12 +1326,15 @@ User.where(:id => 1).includes(:posts).explain yields +EXPLAIN for: SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 ------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------ | 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | ------------------------------------------------------------------------------------ 1 row in set (0.00 sec) + +EXPLAIN for: SELECT `posts`.* FROM `posts` WHERE `posts`.`user_id` IN (1) ------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------- -- cgit v1.2.3 From b30b932447d3ae059c0dac2c5bf0a3a79e0fa54e Mon Sep 17 00:00:00 2001 From: Xavier Noria Date: Fri, 25 Nov 2011 14:58:43 -0800 Subject: finders guide: adds some pointers to help users interpret the output of EXPLAIN --- railties/guides/source/active_record_querying.textile | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 0f1f6eba4c..c4724f182e 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -1344,3 +1344,14 @@ EXPLAIN for: SELECT `posts`.* FROM `posts` WHERE `posts`.`user_id` IN (1) under MySQL. + +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 -- cgit v1.2.3 From a382d60f6abc94b6a965525872f858e48abc00de Mon Sep 17 00:00:00 2001 From: Bogdan Gusiev Date: Wed, 30 Nov 2011 11:03:00 +0200 Subject: ActiveRecord::Relation#pluck method --- railties/guides/source/active_record_querying.textile | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index c4724f182e..073f7c143d 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -1146,6 +1146,15 @@ h3. +select_all+ Client.connection.select_all("SELECT * FROM clients WHERE id = '1'") +h3. +pluck+ + +pluck can be used to query single column from table under model. It accepts column name as argument and returns Array of values of the specified column with corresponding data type. + + +Client.where(:active => true).pluck(:id) # SELECT id FROM clients WHERE clients.active +Client.uniq.pluck(:role) # SELECT DISTINCT role FROM clients + + 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+. -- cgit v1.2.3 From cbeeaa6ea000d7a6e6ae90de5b6dc756d15240ab Mon Sep 17 00:00:00 2001 From: Vijay Dev Date: Thu, 1 Dec 2011 01:23:19 +0530 Subject: expand on pluck docs --- .../guides/source/active_record_querying.textile | 21 ++++++++++++++++++--- 1 file changed, 18 insertions(+), 3 deletions(-) (limited to 'railties/guides/source/active_record_querying.textile') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 073f7c143d..352f23dc01 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -1148,11 +1148,26 @@ Client.connection.select_all("SELECT * FROM clients WHERE id = '1'") h3. +pluck+ -pluck can be used to query single column from table under model. It accepts column name as argument and returns Array of values of the specified column with corresponding data type. +pluck 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. -Client.where(:active => true).pluck(:id) # SELECT id FROM clients WHERE clients.active -Client.uniq.pluck(:role) # SELECT DISTINCT role FROM clients +Client.where(:active => true).pluck(:id) +# SELECT id FROM clients WHERE active = 1 + +Client.uniq.pluck(:role) +# SELECT DISTINCT role FROM clients + + ++pluck+ makes it possible to replace code like + + +Client.select(:id).map { |c| c.id } + + +with + + +Client.pluck(:id) h3. Existence of Objects -- cgit v1.2.3