ruby find_each for permance

find_each(options = {}) public

Yields each record that was found by the find options. The find is performed by find_in_batches with a batch size of 1000 (or as specified by the :batch_size option).

Example:

  Person.find_each(:conditions => "age > 21") do |person|
    person.party_all_night!
  end

Note: This method is only intended to use for batch processing of large amounts of records that wouldn’t fit in memory all at once. If you just need to loop over less than 1000 records, it’s probably better just to use the regular find methods

reference:

http://apidock.com/rails/ActiveRecord/Batches/ClassMethods/find_each

下面这篇文章相当给力

首页 新闻 论坛 问答 博客 招聘 更多 ▼

专栏 圈子 搜索

您还未登录 ! 我的应用 登录 注册

 

reference:

 

http://bellstar.javaeye.com/blog/437132

bellstar

永久域名 http://bellstar.javaeye.com

rails控制器学习笔记 | 重构增强版通用滚动widget，鼠标移入可暂停 ...

2009-07-31

rails查询学习笔记

教程原文http://guides.rubyonrails.org/active_record_querying.html
1、获取数据
.获取第一条、最后一条记录

Ruby代码

1. Model.first   
2. Model.first(options)   
3. Model.find(:first, options)   
5. Model.last   
6. Model.last(options)   
7. Model.find(:last, options) 

Model.first
Model.first(options)
Model.find(:first, options)

Model.last
Model.last(options)
Model.find(:last, options)

.通过id获取记录

Ruby代码

1. Model.find(1, 10, options)   
2. Model.find([1, 10], options) 

Model.find(1, 10, options)
Model.find([1, 10], options)

.find all

Ruby代码

1. Model.all(options) 

Model.all(options)

.对一组数据进行相同操作

Ruby代码

1. User.all.each do |user|   
2.     NewsLetter.weekly_deliver(user)   
3. end

User.all.each do |user|
	NewsLetter.weekly_deliver(user)
end

如果表记录数比较大，这种方式比较耗资源，因为它会一次载入整个表的数据。改用以下这种方式，它每次只载入1000行，然后逐步yield完整个表

Ruby代码

1. User.find_each do |user|   
2.     NewsLetter.weekly_deliver(user)   
3. end

User.find_each do |user|
	NewsLetter.weekly_deliver(user)
end

自定义方式，find_each接受和find同样的options

Ruby代码

1. User.find_each(:batch_size => 5000, :start => 2000) do |user|   
2.     NewsLetter.weekly_deliver(user)   
3. end

User.find_each(:batch_size => 5000, :start => 2000) do |user|
	NewsLetter.weekly_deliver(user)
end

find_in_batches，和find_each相似，但它yield时传递的是model对象数组，而不是单个model对象

Ruby代码

1. Invoice.find_in_batches(:include => :invoice_lines) do |invoices|   
2.     export.add_invoices(invoices)   
3. end

Invoice.find_in_batches(:include => :invoice_lines) do |invoices|
	export.add_invoices(invoices)
end

2、查询条件
通过替换？来传递条件值，可避免SQL注入

Ruby代码

1. Client.first(:conditions => ["orders_count = ?", params[:orders]) 

Client.first(:conditions => ["orders_count = ?", params[:orders])

symbol占位条件

Ruby代码

1. Client.all(:conditions => ["created_at >= :start_date AND created_at <= :end_date", {:start_date => params[:start_date], :end_date => params[:end_date] }]) 

Client.all(:conditions => ["created_at >= :start_date AND created_at <= :end_date", {:start_date => params[:start_date], :end_date => params[:end_date] }])

范围条件 in(集合)

Ruby代码

1. Client.all(:conditions => ["created_at IN (?)", (params[:start_date].to_date)..(params[:end_date].to_date]) 

Client.all(:conditions => ["created_at IN (?)", (params[:start_date].to_date)..(params[:end_date].to_date])

生成sql

Sql代码

1. 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')) 

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'))

如果要生成日期时间，再加上.to_time
params[:start_date].to_date.to_time，生成2007-12-01 00:00:00格式
有上数据库会在以上条件中报错，如Mysql会报查询语句过长的错误，此时可以改成created_at > ? AND created_at < ?的形式
Hash条件

Ruby代码

1. Client.all(:conditions => {:locked => true })  

Client.all(:conditions => {:locked => true }) 

带范围条件

Ruby代码

1. Client.all(:conditons => {:created => (Time.now.midnight - 1.day)..Time.now.midnight}) 

Client.all(:conditons => {:created => (Time.now.midnight - 1.day)..Time.now.midnight})

生成sql

Sql代码

1. SELECT * FROM clients WHERE (clients.created_at BETWEEN '2008-12-21 00:00:00' AND '2008-12-22 00:00:00') 

SELECT * FROM clients WHERE (clients.created_at BETWEEN '2008-12-21 00:00:00' AND '2008-12-22 00:00:00')

集合条件

Ruby代码

1. Client.all(:conditons => {:orders_count => [1,3,5]) 

Client.all(:conditons => {:orders_count => [1,3,5])

生成sql

Sql代码

1. SELECT * FROM clients WHERE (clients.orders_count IN (1,3,5))  

SELECT * FROM clients WHERE (clients.orders_count IN (1,3,5)) 

3、查询选项
排序

Ruby代码

1. #单个排序
2. Client.all(:order => "created_at ASC")   
3. #多个排序
4. Client.all(:order => "orders_count ASC, created_at DESC") 

#单个排序
Client.all(:order => "created_at ASC")
#多个排序
Client.all(:order => "orders_count ASC, created_at DESC")

返回指定字段

Ruby代码

1. Client.all(:select => "viewable_by, locked")   
2. #使用函数
3. Client.all(:select => "DISTINCT(name)") 

Client.all(:select => "viewable_by, locked")
#使用函数
Client.all(:select => "DISTINCT(name)")

限定和偏移Limit and Offset

Ruby代码

1. Client.all(:limit => 5)   
2. #生成
3. SELECT * FROM clients LIMIT 5   
4. Client.all(:limit => 5, :offset => 5)   
5. #生成
6. SELECT * FROM clients LIMIT 5, 5 

Client.all(:limit => 5)
#生成
SELECT * FROM clients LIMIT 5
Client.all(:limit => 5, :offset => 5)
#生成
SELECT * FROM clients LIMIT 5, 5

Group分组

Ruby代码

1. Order.all(:group => "date(created_at)", :order => "created_at") 

Order.all(:group => "date(created_at)", :order => "created_at")

生成sql

Sql代码

1. SELECT * FROM orders GROUP BY date(created_at) 

SELECT * FROM orders GROUP BY date(created_at)

Having

Ruby代码

1. Order.all(:group => "date(created_at)", :having => ["created_at > ?", 1.month.ago) 

Order.all(:group => "date(created_at)", :having => ["created_at > ?", 1.month.ago)

生成sql

Sql代码

1. SELECT * FROM orders GROUP BY date(created_at) HAVING created_at > '2009-01-15'

SELECT * FROM orders GROUP BY date(created_at) HAVING created_at > '2009-01-15' 

只读

Ruby代码

1. client = Client.first(:readonly => true)   
2. client.locked = false
3. client.save   
4. #对只读对象进行保存将会触发ActiveRecord::ReadOnlyRecord异常

client = Client.first(:readonly => true)
client.locked = false
client.save
#对只读对象进行保存将会触发ActiveRecord::ReadOnlyRecord异常

更新时锁定记录
乐观锁Optimistic Locking
为使用乐观锁，须在表里建一个lock_version的字段，每次更新记录时，ActiveRecord自动递增lock_version的值，

Ruby代码

1. c1 = Client.find(1) c2 = Client.find(1) c1.name = "Michael" c1.save c2.name = "should fail" c2.save # Raises a ActiveRecord::StaleObjectError

c1 = Client.find(1) c2 = Client.find(1) c1.name = "Michael" c1.save c2.name = "should fail" c2.save # Raises a ActiveRecord::StaleObjectError 

备注：You must ensure that your database schema defaults the lock_version column to 0.
This behavior can be turned off by setting ActiveRecord::Base.lock_optimistically = false.
指定乐观锁字段名

Ruby代码

1. class Client < ActiveRecord::Base set_locking_column :lock_client_column end

class Client < ActiveRecord::Base set_locking_column :lock_client_column end 

悲观锁Pessimistic Locking
悲观锁定由数据库直接提供

Ruby代码

1. Item.transaction do
2.     i = Item.first(:lock => true)   
3.     i.name = 'Jones'
4.     i.save   
5. end

Item.transaction do 
	i = Item.first(:lock => true)
	i.name = 'Jones'
	i.save
end

Mysql执行返回
SQL (0.2ms) BEGIN Item Load (0.3ms) SELECT * FROM `items` LIMIT 1 FOR UPDATE Item Update (0.4ms) UPDATE `items` SET `updated_at` = '2009-02-07 18:05:56', `name` = 'Jones' WHERE `id` = 1 SQL (0.8ms) COMMIT
为特定数据库加入原始的lock声明
为Mysql的锁定声明为共享模式，即锁定时仍然可读
Item.transaction do  i = Item.find(1, :lock => "LOCK IN SHARE MODE")  i.increment!(:views) end
4、关联表

Ruby代码

1. Client.all(:joins => "LEFT OUTER JOIN address ON addresses.client_id = clients.id') 

Client.all(:joins => "LEFT OUTER JOIN address ON addresses.client_id = clients.id')

生成sql

Sql代码

1. SELECT clients.* FROM clients LEFT OUTER JOIN addresses ON addresses.client_id = clients.id  

SELECT clients.* FROM clients LEFT OUTER JOIN addresses ON addresses.client_id = clients.id 

使用Array、Hash、Named Associations关联表
有如下model

Ruby代码

1. class Category < ActiveRecord::Base    
2.     has_many :posts
3. end
4. class Post < ActiveRecord::Base    
5.     belongs_to :category
6.     has_many :comments
7.     has_many :tags
8. end
9. class Comments <ActiveRecord::Base    
10.     belongs_to :post
11.     has_one :guest
12. end
13. class Guest < ActiveRecord::Base    
14.     belongs_to :comment
15. end

class Category < ActiveRecord::Base 
	has_many :posts 
end 
class Post < ActiveRecord::Base 
	belongs_to :category  
	has_many :comments  	
	has_many :tags 
end 
class Comments <ActiveRecord::Base 
	belongs_to :post  
	has_one :guest 
end 
class Guest < ActiveRecord::Base 
	belongs_to :comment 
end 

Ruby代码

1. #关联一个关系
2. Category.all :joins => :posts
3. #关联多个关系
4. Post.all :joins => [:category, :comments]    
5. #嵌套关联
6. Category.all :joins => {:posts => [{:comments => :guest}, :tags]}  

#关联一个关系
Category.all :joins => :posts
#关联多个关系
Post.all :joins => [:category, :comments] 
#嵌套关联
Category.all :joins => {:posts => [{:comments => :guest}, :tags]} 

为关联查询结果设定条件

Ruby代码

1. time_range = (Time.now.midnight - 1.day)..Time.now.midnight Client.all :joins => :orders, :conditions => {'orders.created_at' => time_ran   
2. #或者
3. time_range = (Time.now.midnight - 1.day)..Time.now.midnight Client.all :joins => :orders, :conditions => {:orders => {:created_at => time_range}}  

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、优化载入
以下代码，需要执行1 + 10次sql

Ruby代码

1. clients = Client.all(:limit => 10) clients.each do |client|    
2.     puts client.address.postcode    
3. end

clients = Client.all(:limit => 10) clients.each do |client| 
	puts client.address.postcode 
end 

优化：

Ruby代码

1. clients = Client.all(:include => :address, :limit => 10)    
2. clients.each do |client|    
3.     puts client.address.postcode    
4. end

clients = Client.all(:include => :address, :limit => 10) 
clients.each do |client| 
	puts client.address.postcode 
end 

一次性载入post的所有分类和评论

Ruby代码

1. Post.all :include => [:category, :comments]  

Post.all :include => [:category, :comments] 

载入category为1的所有post和cooment及tag

Ruby代码

1. Category.find 1, :include => {:posts => [{:comments => :guest}, :tags]}  

Category.find 1, :include => {:posts => [{:comments => :guest}, :tags]} 

6、动态查询

Ruby代码

1. Client.find_by_name("Ryan")   
2. Client.find_all_by_name("Ryan")   
4. #！方法，没有记录时抛出ActiveRecord::RecordNotFound异常
5. Client.find_by_name!("Ryan")   
7. #查询多个字段
8. Client.find_by_name_and_locked("Ryan", true)   
10. #查询不到时就创建并保存
11. Client.find_or_create_by_name(params[:name])   
12. #查询不到时创建一个实例，但不保存
13. Client.find_or_initialize_by_name('Ryan') 

Client.find_by_name("Ryan")
Client.find_all_by_name("Ryan")

#！方法，没有记录时抛出ActiveRecord::RecordNotFound异常
Client.find_by_name!("Ryan")

#查询多个字段
Client.find_by_name_and_locked("Ryan", true)

#查询不到时就创建并保存
Client.find_or_create_by_name(params[:name])
#查询不到时创建一个实例，但不保存
Client.find_or_initialize_by_name('Ryan')

7、find_by_sql

Ruby代码

1. Client.find_by_sql("SELECT * FROM clients INNER JOIN orders ON clients.id = orders.client_id ORDER clients.created_at desc")  

Client.find_by_sql("SELECT * FROM clients INNER JOIN orders ON clients.id = orders.client_id ORDER clients.created_at desc") 

8、select_all
和find_by_sql类似，但不会用model实例化返回记录，你会得到一个hash数组

Ruby代码

1. Client.connection.select_all("SELECT * FROM clients WHERE id = '1'")  

Client.connection.select_all("SELECT * FROM clients WHERE id = '1'") 

9、判断记录是否存在

Ruby代码

1. #通过id来查询
2. Client.exists?(1)   
4. Client.exists?(1, 2, 3)   
5. #or
6. Client.exists?([1,2,3])   
8. #通过其他条件来查询
9. Client.exists?(:conditions => "first_name = 'Ryan'")   
11. #没有参数时，则：表是空的 ? false : true
12. Client.exists? 

#通过id来查询
Client.exists?(1)

Client.exists?(1, 2, 3)
#or
Client.exists?([1,2,3])

#通过其他条件来查询
Client.exists?(:conditions => "first_name = 'Ryan'")

#没有参数时，则：表是空的 ? false : true
Client.exists?

10、计算

Ruby代码

1. #求结果集条数
2. Client.count(:conditons => "first_name = 'Ryan'")   
4. #求某个字段非空白的条数
5. Client.count(:age)   
7. #平均值
8. Client.average("orders_count")   
10. #求最小值
11. Client.minimum("age")   
13. #求最大值
14. Client.maximum("age")   
16. #求和
17. Client.sum("orders_count") 

#求结果集条数
Client.count(:conditons => "first_name = 'Ryan'")

#求某个字段非空白的条数
Client.count(:age)

#平均值
Client.average("orders_count")

#求最小值
Client.minimum("age")

#求最大值
Client.maximum("age")

#求和
Client.sum("orders_count")

rails控制器学习笔记 | 重构增强版通用滚动widget，鼠标移入可暂停 ...

• 15:09
• 浏览 (767)
• 评论 (0)
• 分类: rails
• 相关推荐

评论

发表评论

表情图标

字体颜色: 标准深红红色橙色棕色黄色绿色橄榄青色蓝色深蓝靛蓝紫色灰色白色黑色 字体大小: 标准1 (xx-small)2 (x-small)3 (small)4 (medium)5 (large)6 (x-large)7 (xx-large) 对齐: 标准居左居中居右

提示：选择您需要装饰的文字, 按上列按钮即可添加上相应的标签

您还没有登录，请登录后发表评论(快捷键 Alt+S / Ctrl+Enter)

bellstar

• 浏览: 23918 次
• 性别:
• 来自: 上海
• 
• 详细资料 留言簿

搜索本博客

最近访客 >>更多访客

Richmond1979

oleiowang

maleo

changcheng3920

博客分类

• 全部博客 (59)
• javascript (29)
• SUI (5)
• qooxdoo (5)
• ruby (8)
• rails (6)
• linux (6)
• mac os (3)
• editor (2)
• sinatra (1)
• html-css (1)

我的相册

sinatra项目练习
共 18 张

我的留言簿 >>更多留言

• 你填的中专学历有没有进大公司？？我想进大公司，但是我想知道大公司承认成人高考不
  -- by 好美丽
• 我是高中毕业的，对自己的职业前景很是迷茫啊，会jdbc,会ssh，感觉太少了。 路 ...
  -- by 好美丽

其他分类

• 我的收藏 (185)
• 我的论坛主题贴 (26)
• 我的所有论坛贴 (51)
• 我的精华良好贴 (1)
• 我解决的问题 (6)

最近加入圈子

• Google App Engine
• Ruby off Rails
• phprpc/hprose
• Vim编辑器
• GT-Grid

存档

• 2010-12 (1)
• 2010-09 (1)
• 2010-07 (1)
• 更多存档...

评论排行榜

• javascript总结（一）有关框架
• javascript总结（四）ajax局部刷新与RPC
• javascript总结（二）文件组织与代码组织
• javascript总结（三）js与FLASH的交互
• javascript总结（五）获取设置元素样式与监 ...

• 
• 

---

声明：JavaEye文章版权属于作者，受法律保护。没有作者书面许可不得转载。若作者同意转载，必须以超链接形式标明文章原始出处和作者。
© 2003-2010 JavaEye.com. All rights reserved. 上海炯耐计算机软件有限公司 [ 沪ICP备05023328号 ]