diff options
author | Ryan Bigg <radarlistener@gmail.com> | 2010-11-29 15:08:35 +1100 |
---|---|---|
committer | Ryan Bigg <radarlistener@gmail.com> | 2010-11-29 15:08:35 +1100 |
commit | 964c2025ff7b4d22902ed8ee86c3e2858a4cf3ba (patch) | |
tree | b4e12825c9bf31c15dd25a3582e3e589bb00a5ed | |
parent | 390de62cacb6b3c89916b1146cdf204a5bab0703 (diff) | |
download | rails-964c2025ff7b4d22902ed8ee86c3e2858a4cf3ba.tar.gz rails-964c2025ff7b4d22902ed8ee86c3e2858a4cf3ba.tar.bz2 rails-964c2025ff7b4d22902ed8ee86c3e2858a4cf3ba.zip |
Use ARel for Range Conditions section, remove Date & Time section because users should *never* do that.
-rw-r--r-- | railties/guides/source/active_record_querying.textile | 60 |
1 files changed, 7 insertions, 53 deletions
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 <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: |