diff options
Diffstat (limited to 'railties/guides/source/active_record_querying.textile')
| -rw-r--r-- | railties/guides/source/active_record_querying.textile | 113 |
1 files changed, 105 insertions, 8 deletions
diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 579a323d57..082f9eda7d 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -57,6 +57,7 @@ The methods are: * +group+ * +order+ * +reorder+ +* +reverse_order+ * +limit+ * +offset+ * +joins+ @@ -255,7 +256,7 @@ The above will yield the supplied block with +1000+ invoices every time. h3. Conditions -The +find+ 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. +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. h4. Pure String Conditions @@ -465,7 +466,7 @@ To apply a +GROUP BY+ clause to the SQL fired by the finder, you can specify the For example, if you want to find a collection of the dates orders were created on: <ruby> -Order.group("date(created_at)").order("created_at") +Order.select("date(created_at) as ordered_date, sum(price) as total_price").group("date(created_at)") </ruby> And this will give you a single +Order+ object for each date where there are orders in the database. @@ -473,7 +474,7 @@ And this will give you a single +Order+ object for each date where there are ord The SQL that would be executed would be something like this: <sql> -SELECT * FROM orders GROUP BY date(created_at) ORDER BY created_at +SELECT date(created_at) as ordered_date, sum(price) as total_price FROM orders GROUP BY date(created_at) </sql> h3. Having @@ -483,16 +484,16 @@ SQL uses the +HAVING+ clause to specify conditions on the +GROUP BY+ fields. You For example: <ruby> -Order.group("date(created_at)").having("created_at > ?", 1.month.ago) +Order.select("date(created_at) as ordered_date, sum(price) as total_price").group("date(created_at)").having("sum(price) > ?", 100) </ruby> The SQL that would be executed would be something like this: <sql> -SELECT * FROM orders GROUP BY date(created_at) HAVING created_at > '2009-01-15' +SELECT date(created_at) as ordered_date, sum(price) as total_price FROM orders GROUP BY date(created_at) HAVING sum(price) > 100 </sql> -This will return single order objects for each day, but only for the last month. +This will return single order objects for each day, but only those that are ordered more than $100 in a day. h3. Overriding Conditions @@ -550,6 +551,32 @@ In case the +reorder+ clause is not used, the SQL executed would be: SELECT * FROM posts WHERE id = 10 ORDER BY posted_at DESC </sql> +h4. +reverse_order+ + +The +reverse_order+ method reverses the ordering clause if specified. + +<ruby> +Client.where("orders_count > 10").order(:name).reverse_order +</ruby> + +The SQL that would be executed: +<sql> +SELECT * FROM clients WHERE orders_count > 10 ORDER BY name DESC +</sql> + +If no ordering clause is specified in the query, the +reverse_order+ orders by the primary key in reverse order. + +<ruby> +Client.where("orders_count > 10").reverse_order +</ruby> + +The SQL that would be executed: +<sql> +SELECT * FROM clients WHERE orders_count > 10 ORDER BY clients.id DESC +</sql> + +This method accepts *no* arguments. + 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. @@ -675,7 +702,7 @@ class Post < ActiveRecord::Base has_many :tags end -class Comments < ActiveRecord::Base +class Comment < ActiveRecord::Base belongs_to :post has_one :guest end @@ -683,6 +710,10 @@ end class Guest < ActiveRecord::Base belongs_to :comment end + +class Tag < ActiveRecord::Base + belongs_to :post +end </ruby> Now all of the following will produce the expected join queries using +INNER JOIN+: @@ -700,6 +731,8 @@ 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)"). + h5. Joining Multiple Associations <ruby> @@ -714,18 +747,40 @@ SELECT posts.* FROM posts INNER JOIN comments ON comments.post_id = posts.id </sql> +Or, in English: "return all posts that have a category and at least one comment". Note again that posts with multiple comments will show up multiple times. + h5. Joining Nested Associations (Single Level) <ruby> Post.joins(:comments => :guest) </ruby> +This produces: + +<sql> +SELECT posts.* FROM posts + INNER JOIN comments ON comments.post_id = posts.id + INNER JOIN guests ON guests.comment_id = comments.id +</sql> + +Or, in English: "return all posts that have a comment made by a guest." + h5. Joining Nested Associations (Multiple Level) <ruby> Category.joins(:posts => [{:comments => :guest}, :tags]) </ruby> +This produces: + +<sql> +SELECT categories.* FROM categories + INNER JOIN posts ON posts.category_id = categories.id + INNER JOIN comments ON comments.post_id = posts.id + INNER JOIN guests ON guests.comment_id = comments.id + INNER JOIN tags ON tags.post_id = posts.id +</sql> + h4. Specifying Conditions on the Joined Tables You can specify conditions on the joined tables using the regular "Array":#array-conditions and "String":#pure-string-conditions conditions. "Hash conditions":#hash-conditions provides a special syntax for specifying conditions for the joined tables: @@ -910,6 +965,47 @@ Using a class method is the preferred way to accept arguments for scopes. These category.posts.1_week_before(time) </ruby> +h4. Working with scopes + +Where a relational object is required, the +scoped+ method may come in handy. This will return an +ActiveRecord::Relation+ object which can have further scoping applied to it afterwards. A place where this may come in handy is on associations + +<ruby> +client = Client.find_by_first_name("Ryan") +orders = client.orders.scoped +</ruby> + +With this new +orders+ object, we are able to ascertain that this object can have more scopes applied to it. For instance, if we wanted to return orders only in the last 30 days at a later point. + +<ruby> +orders.where("created_at > ?", 30.days.ago) +</ruby> + +h4. 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. + +<ruby> +class Client < ActiveRecord::Base + default_scope where("removed_at IS NULL") +end +</ruby> + +When queries are executed on this model, the SQL query will now look something like this: + +<sql> +SELECT * FROM clients WHERE removed_at IS NULL +</sql> + +h4. 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. + +<ruby> +Client.unscoped.all +</ruby> + +This method removes all scoping and will do a normal query on the table. + h3. Dynamic Finders For every field (also known as an attribute) you define in your table, Active Record provides a finder method. If you have a field called +first_name+ on your +Client+ model for example, you get +find_by_first_name+ and +find_all_by_first_name+ for free from Active Record. If you have a +locked+ field on the +Client+ model, you also get +find_by_locked+ and +find_all_by_locked+ methods. @@ -1091,7 +1187,8 @@ For options, please see the parent section, "Calculations":#calculations. h3. Changelog -* December 23 2010: Add documentation for the +scope+ method. "Ryan Bigg":http://ryanbigg.com +* 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 |