1, access to data
Get first and last record
Copy Code code as follows:
Model.first
Model.first (Options)
Model.find (: I, Options)
Model.last
Model.last (Options)
Model.find (: Last, options)
Get Records by ID
Copy Code code as follows:
Model.find (1, options)
Model.find ([1], options)
. Find All
Copy Code code as follows:
Perform the same operation on a set of data
Copy Code code as follows:
User.all.each do |user|
Newsletter.weekly_deliver (user)
End
This is a resource-intensive method if the table records are large, because it loads the entire table's data at once. Instead, it loads only 1000 rows at a time, and then progressively yield the full table
Copy Code code as follows:
User.find_each do |user|
Newsletter.weekly_deliver (user)
End
Custom way, Find_each accept and find the same options
Copy Code code as follows:
User.find_each (: Batch_size => 5000,: Start =>) do |user|
Newsletter.weekly_deliver (user)
End
Find_in_batches, similar to Find_each, but it yield the model object array instead of a single model object
Copy Code code as follows:
Invoice.find_in_batches (: Include =>: invoice_lines) do |invoices|
Export.add_invoices (Invoices)
End
2, query conditions
by replacing? To pass the conditional value , to avoid SQL injection
Copy Code code as follows:
Client.first (: Conditions => ["Orders_count =?", Params[:orders])
symbol Placeholder
Copy Code code as follows:
Client.all (: Conditions => ["Created_at >=: start_date and Created_at <=: End_date", {: Start_date =>, Params[:s Tart_date],: end_date => params[:end_date]})
range condition in (set)
Copy Code code as follows:
Client.all (: Conditions => ["Created_at in (?)", (Params[:start_date].to_date). (Params[:end_date].to_date])
Generate SQL
Copy Code code as follows:
SELECT * from the 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 ')
If you want to generate a date time, Add. to_time
Copy Code code as follows:
Params[:start_date].to_date.to_time, build 2007-12-01 00:00:00 format
Have on the database will be in the above conditions, such as MySQL will report the query statement too long error, at this time can be changed to Created_at >? The form of < Created_at
Hash condition
Copy Code code as follows:
Client.all (: Conditions => {: Locked => true})
Band-wide conditions
Copy Code code as follows:
Client.all (: Conditons => {: Created => (time.now.midnight-1.day). Time.now.midnight})
Generate SQL
Copy Code code as follows:
SELECT * from Clients WHERE (clients.created_at BETWEEN ' 2008-12-21 00:00:00 ' and ' 2008-12-22 00:00:00 ')
Set Conditions
Copy Code code as follows:
Client.all (: conditons => {: Orders_count => [1,3,5])
Generate SQL
Copy Code code as follows:
SELECT * from Clients WHERE (Clients.orders_count in (1,3,5))
3, query options
Sort
Copy Code code as follows:
#单个排序
Client.all (: Order => "Created_at ASC")
#多个排序
Client.all (: Order => "Orders_count ASC, Created_at DESC")
Returns the specified field
Copy Code code as follows:
Client.all (: Select => "viewable_by, Locked")
#使用函数
Client.all (: Select => "DISTINCT (name)")
Qualifying and offsetting limit and offset
Copy Code code as follows:
Client.all (: Limit => 5)
#生成
SELECT * FROM clients LIMIT 5
Client.all (: Limit => 5, offset => 5)
#生成
SELECT * from Clients LIMIT 5, 5
Group Group
Copy Code code as follows:
Order.all (: Group => "date (CREATED_AT)": Order => "Created_at")
Generate SQL
Copy Code code as follows:
SELECT * From to Orders GROUP by date (CREATED_AT)
Having
Copy Code code as follows:
Order.all (: Group => "date (created_at)": Having => ["Created_at >?", 1.month.ago)
Generate SQL
Copy Code code as follows:
SELECT * From to Orders GROUP by date (CREATED_AT) has created_at > ' 2009-01-15 '
Read-only
Copy Code code as follows:
Client = Client.first (: readonly => true)
Client.locked = False
Client.save
#对只读对象进行保存将会触发ActiveRecord:: Readonlyrecord exception
Lock records when updating
optimistic lock optimistic locking
To use optimistic locks, you must build a lock_version field in the table, ActiveRecord automatically increments the Lock_version value each time the record is updated.
Copy Code code as follows:
C1 = Client.find (1) c2 = Client.find (1) c1.name = "Michael" c1.save c2.name = "should fail" C2.save # raises a Activerecor D::staleobjecterror
Note: You must ensure this your database schema defaults the lock_version column to 0.
This behavior can is turned off by setting activerecord::base.lock_optimistically = False.
Specify optimistic Lock field name
Copy Code code as follows:
Class Client < ActiveRecord::Base Set_locking_column:lock_client_column End
pessimistic lock pessimistic locking
Pessimistic locking is provided directly by the database
Copy Code code as follows:
Item.transaction do
i = Item.first (: Lock => True)
I.name = ' Jones '
I.save
End
MySQL execution return
Copy Code code as follows:
SQL (0.2ms) BEGIN Item Load (0.3ms) SELECT * from ' items ' LIMIT 1 for Update Item Update (0.4MS) Update ' items ' SET ' Updat Ed_at ' = ' 2009-02-07 18:05:56 ', ' name ' = ' Jones ' WHERE ' id ' = 1 SQL (0.8ms) COMMIT
Add the original lock declaration for a specific database
The lock for MySQL is declared as shared mode, which is still readable when locked
Copy Code code as follows:
item.transaction Do i = Item.find (1,: Lock => "lock in SHARE MODE") i.increment! (: Views) end
4. Correlation table
Copy Code code as follows:
Client.all (: Joins => "left OUTER JOIN address on addresses.client_id = Clients.id ')
Generate SQL
Copy Code code as follows:
SELECT clients.* from clients left OUTER JOIN addresses on addresses.client_id = Clients.id
Using array, Hash, Named Associations Association tables
Has the following model
Copy Code code as follows:
Class Category < ActiveRecord::Base
Has_many:p OSTs
End
Class Post < ActiveRecord::Base
Belongs_to:category
Has_many:comments
Has_many:tags
End
Class Comments <activerecord::base
Belongs_to:p OST
Has_one:guest
End
Class Guest < ActiveRecord::Base
Belongs_to:comment
End
Copy Code code as follows:
#关联一个关系
Category.all:joins =>:p OSTs
#关联多个关系
Post.all:joins => [: Category,: Comments]
#嵌套关联
Category.all:joins => {:p OSTs => [{: Comments =>: Guest},: tags]}
Set criteria for associated query results
Copy Code code as follows:
Time_range = (time.now.midnight-1.day). Time.now.midnight Client.all:joins =>: Orders,: Conditions => {' Orders.created_at ' => Time_ran
#或者
Time_range = (time.now.midnight-1.day). Time.now.midnight Client.all:joins =>: Orders,: Conditions => {: Orders => {: Created_at => Time_range}}
5. Optimize load
The following code needs to execute 1 + 10 times SQL
Copy Code code as follows:
Clients = Client.all (: Limit =>) Clients.each do |client|
Puts Client.address.postcode
End
Optimization:
Copy Code code as follows:
Clients = Client.all (: Include =>: Address,: Limit => 10)
Clients.each do |client|
Puts Client.address.postcode
End
All categories and comments for a one-time load post
Copy Code code as follows:
Post.all:include => [: Category,: Comments]
Load all post and cooment and tag for category 1
Copy Code code as follows:
Category.find 1,: include => {:p osts => [{: Comments =>: Guest},: tags]}
6. Dynamic Query
Copy Code code as follows:
Client.find_by_name ("Ryan")
Client.find_all_by_name ("Ryan")
#！ method, throw Activerecord::recordnotfound exception when no record
client.find_by_name! ("Ryan")
#查询多个字段
Client.find_by_name_and_locked ("Ryan", True)
#查询不到时就创建并保存
Client.find_or_create_by_name (Params[:name])
#查询不到时创建一个实例, but not saved
Client.find_or_initialize_by_name (' Ryan ')
7, Find_by_sql
Copy Code code as follows:
Client.find_by_sql ("SELECT * from clients INNER JOIN orders ' clients.id = orders.client_id order clients.created_at desc ")
8, Select_all
Similar to Find_by_sql, but does not return records with model instantiation, you will get a hash array
Copy Code code as follows:
Client.connection.select_all ("SELECT * from clients WHERE id = ' 1 '")
9, to determine whether the existence of records
Copy Code code as follows:
#通过id来查询
Client.exists? (1)
Client.exists? (1, 2, 3)
#or
Client.exists? ([1,2,3])
#通过其他条件来查询
Client.exists? (: Conditions => "first_name = ' Ryan '")
#没有参数时: Is the table empty? False:true
Client.exists?
10. Calculation
Copy Code code as follows:
#求结果集条数
Client.count (: conditons => "first_name = ' Ryan '")
#求某个字段非空白的条数
Client.count (: Age)
#平均值
Client.average ("Orders_count")
#求最小值
Client.minimum ("Age")
#求最大值
Client.maximum ("Age")
#求和
Client.sum ("Orders_count")
