From 964c2025ff7b4d22902ed8ee86c3e2858a4cf3ba Mon Sep 17 00:00:00 2001 From: Ryan Bigg Date: Mon, 29 Nov 2010 15:08:35 +1100 Subject: Use ARel for Range Conditions section, remove Date & Time section because users should *never* do that. --- .../guides/source/active_record_querying.textile | 60 +++------------------- 1 file changed, 7 insertions(+), 53 deletions(-) (limited to 'railties/guides') diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile index 328439fdb8..0447013e24 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 ActiveRecord::Relation. @@ -103,7 +104,7 @@ h5. +first+ client = Client.first -=> # "Lifo"> +=> # SQL equivalent of the above is: @@ -120,7 +121,7 @@ h5. +last+ client = Client.last -=> # "Russel"> +=> # 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: 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: -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)) -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: -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') -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: - - -Client.where("created_at IN (?)", - (params[:start_date].to_date.to_time)..(params[:end_date].to_date.to_time)) - - - -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')) - - -This could possibly cause your database server to raise an unexpected error, for example MySQL will throw back this error: - - -Got a packet bigger than 'max_allowed_packet' bytes: _query_ - - -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: - - -Client.where( - "created_at > ? AND created_at < ?", params[:start_date], params[:end_date]) - - -You can also use the greater-than-or-equal-to and less-than-or-equal-to like this: - - -Client.where( - "created_at >= ? AND created_at <= ?", params[:start_date], params[:end_date]) - - -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: -- cgit v1.2.3