diff options
Diffstat (limited to 'railties/guides/source/active_record_querying.textile')
-rw-r--r-- | railties/guides/source/active_record_querying.textile | 64 |
1 files changed, 9 insertions, 55 deletions
diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 328439fdb8..b9ad7ccbd2 100644 --- a/railties/guides/source/active_record_querying.textile +++ b/railties/guides/source/active_record_querying.textile @@ -65,6 +65,7 @@ The methods are: * +lock+ * +readonly+ * +from+ +* +having+ All of the above methods return an instance of <tt>ActiveRecord::Relation</tt>. @@ -103,7 +104,7 @@ h5. +first+ <ruby> client = Client.first -=> #<Client id: 1, first_name: => "Lifo"> +=> #<Client id: 1, first_name: "Lifo"> </ruby> SQL equivalent of the above is: @@ -120,7 +121,7 @@ h5. +last+ <ruby> client = Client.last -=> #<Client id: 221, first_name: => "Russel"> +=> #<Client id: 221, first_name: "Russel"> </ruby> SQL equivalent of the above is: @@ -231,7 +232,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, or perhaps from the user's level status somewhere? The find then becomes something like: +Now what if that number could vary, say as an argument from somewhere? The find then becomes something like: <ruby> Client.where("orders_count = ?", params[:orders]) @@ -279,62 +280,15 @@ h5(#array-range_conditions). Range Conditions If you're looking for a range inside of a table (for example, users created in a certain timeframe) you can use the conditions option coupled with the +IN+ SQL statement for this. If you had two dates coming in from a controller you could do something like this to look for a range: <ruby> -Client.where("created_at IN (?)", - (params[:start_date].to_date)..(params[:end_date].to_date)) +Client.where(:created_at => (params[:start_date].to_date)..(params[:end_date].to_date)) </ruby> -This would generate the proper query which is great for small ranges but not so good for larger ranges. For example if you pass in a range of date objects spanning a year that's 365 (or possibly 366, depending on the year) strings it will attempt to match your field against. +This query will generate something similar to the following SQL: <sql> -SELECT * FROM users WHERE (created_at IN - ('2007-12-31','2008-01-01','2008-01-02','2008-01-03','2008-01-04','2008-01-05', - '2008-01-06','2008-01-07','2008-01-08','2008-01-09','2008-01-10','2008-01-11', - '2008-01-12','2008-01-13','2008-01-14','2008-01-15','2008-01-16','2008-01-17', - '2008-01-18','2008-01-19','2008-01-20','2008-01-21','2008-01-22','2008-01-23',... - ‘2008-12-15','2008-12-16','2008-12-17','2008-12-18','2008-12-19','2008-12-20', - '2008-12-21','2008-12-22','2008-12-23','2008-12-24','2008-12-25','2008-12-26', - '2008-12-27','2008-12-28','2008-12-29','2008-12-30','2008-12-31')) + SELECT "clients".* FROM "clients" WHERE ("clients"."created_at" BETWEEN '2010-09-29' AND '2010-11-30') </sql> -h5. Time and Date Conditions - -Things can get *really* messy if you pass in Time objects as it will attempt to compare your field to *every second* in that range: - -<ruby> -Client.where("created_at IN (?)", - (params[:start_date].to_date.to_time)..(params[:end_date].to_date.to_time)) -</ruby> - -<sql> -SELECT * FROM users WHERE (created_at IN - ('2007-12-01 00:00:00', '2007-12-01 00:00:01' ... - '2007-12-01 23:59:59', '2007-12-02 00:00:00')) -</sql> - -This could possibly cause your database server to raise an unexpected error, for example MySQL will throw back this error: - -<shell> -Got a packet bigger than 'max_allowed_packet' bytes: _query_ -</shell> - -Where _query_ is the actual query used to get that error. - -In this example it would be better to use greater-than and less-than operators in SQL, like so: - -<ruby> -Client.where( - "created_at > ? AND created_at < ?", params[:start_date], params[:end_date]) -</ruby> - -You can also use the greater-than-or-equal-to and less-than-or-equal-to like this: - -<ruby> -Client.where( - "created_at >= ? AND created_at <= ?", params[:start_date], params[:end_date]) -</ruby> - -Just like in Ruby. If you want a shorter syntax be sure to check out the "Hash Conditions":#hash-conditions section later on in the guide. - h4. 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: @@ -385,7 +339,7 @@ SELECT * FROM clients WHERE (clients.orders_count IN (1,3,5)) h4. Ordering -To retrieve records from the database in a specific order, you can specify the +:order+ option to the +find+ call. +To retrieve records from the database in a specific order, you can use the +order+ method. For example, if you're getting a set of records and want to order them in ascending order by the +created_at+ field in your table: @@ -496,7 +450,7 @@ 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.group("date(created_at)").having("created_at > ?", 1.month.ago) </ruby> The SQL that would be executed would be something like this: |