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、获取数据
.获取第一条、最后一条记录
- Model.first
- Model.first(options)
- Model.find(:first, options)
- Model.last
- Model.last(options)
- Model.find(:last, options)
Model.first Model.first(options) Model.find(:first, options) Model.last Model.last(options) Model.find(:last, options)
.通过id获取记录
- Model.find(1, 10, options)
- Model.find([1, 10], options)
Model.find(1, 10, options) Model.find([1, 10], options)
.find all
- Model.all(options)
Model.all(options)
.对一组数据进行相同操作
- User.all.each do |user|
- NewsLetter.weekly_deliver(user)
- end
User.all.each do |user| NewsLetter.weekly_deliver(user) end
如果表记录数比较大,这种方式比较耗资源,因为它会一次载入整个表的数据。改用以下这种方式,它每次只载入1000行,然后逐步yield完整个表
- User.find_each do |user|
- NewsLetter.weekly_deliver(user)
- end
User.find_each do |user| NewsLetter.weekly_deliver(user) end
自定义方式,find_each接受和find同样的options
- User.find_each(:batch_size => 5000, :start => 2000) do |user|
- NewsLetter.weekly_deliver(user)
- end
User.find_each(:batch_size => 5000, :start => 2000) do |user| NewsLetter.weekly_deliver(user) end
find_in_batches,和find_each相似,但它yield时传递的是model对象数组,而不是单个model对象
- Invoice.find_in_batches(:include => :invoice_lines) do |invoices|
- export.add_invoices(invoices)
- end
Invoice.find_in_batches(:include => :invoice_lines) do |invoices| export.add_invoices(invoices) end
2、查询条件
通过替换?来传递条件值,可避免SQL注入
- Client.first(:conditions => ["orders_count = ?", params[:orders])
Client.first(:conditions => ["orders_count = ?", params[:orders])
symbol占位条件
- 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(集合)
- 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
- 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条件
- Client.all(:conditions => {:locked => true })
Client.all(:conditions => {:locked => true })
带范围条件
- 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
- 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')
集合条件
- Client.all(:conditons => {:orders_count => [1,3,5])
Client.all(:conditons => {:orders_count => [1,3,5])
生成sql
- SELECT * FROM clients WHERE (clients.orders_count IN (1,3,5))
SELECT * FROM clients WHERE (clients.orders_count IN (1,3,5))
3、查询选项
排序
- #单个排序
- Client.all(:order => "created_at ASC")
- #多个排序
- Client.all(:order => "orders_count ASC, created_at DESC")
#单个排序 Client.all(:order => "created_at ASC") #多个排序 Client.all(:order => "orders_count ASC, created_at DESC")
返回指定字段
- Client.all(:select => "viewable_by, locked")
- #使用函数
- Client.all(:select => "DISTINCT(name)")
Client.all(:select => "viewable_by, locked") #使用函数 Client.all(:select => "DISTINCT(name)")
限定和偏移Limit and Offset
- Client.all(:limit => 5)
- #生成
- SELECT * FROM clients LIMIT 5
- Client.all(:limit => 5, :offset => 5)
- #生成
- 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分组
- Order.all(:group => "date(created_at)", :order => "created_at")
Order.all(:group => "date(created_at)", :order => "created_at")
生成sql
- SELECT * FROM orders GROUP BY date(created_at)
SELECT * FROM orders GROUP BY date(created_at)
Having
- 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
- 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'
只读
- client = Client.first(:readonly => true)
- client.locked = false
- client.save
- #对只读对象进行保存将会触发ActiveRecord::ReadOnlyRecord异常
client = Client.first(:readonly => true) client.locked = false client.save #对只读对象进行保存将会触发ActiveRecord::ReadOnlyRecord异常
更新时锁定记录
乐观锁Optimistic Locking
为使用乐观锁,须在表里建一个lock_version的字段,每次更新记录时,ActiveRecord自动递增lock_version的值,
- 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.
指定乐观锁字段名
- class Client < ActiveRecord::Base set_locking_column :lock_client_column end
class Client < ActiveRecord::Base set_locking_column :lock_client_column end
悲观锁Pessimistic Locking
悲观锁定由数据库直接提供
- Item.transaction do
- i = Item.first(:lock => true)
- i.name = 'Jones'
- i.save
- 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、关联表
- 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
- 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
- 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
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
- #关联一个关系
- Category.all :joins => :posts
- #关联多个关系
- Post.all :joins => [:category, :comments]
- #嵌套关联
- Category.all :joins => {:posts => [{:comments => :guest}, :tags]}
#关联一个关系 Category.all :joins => :posts #关联多个关系 Post.all :joins => [:category, :comments] #嵌套关联 Category.all :joins => {:posts => [{:comments => :guest}, :tags]}
为关联查询结果设定条件
- 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}}
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
- clients = Client.all(:limit => 10) clients.each do |client|
- puts client.address.postcode
- end
clients = Client.all(:limit => 10) clients.each do |client| puts client.address.postcode end
优化:
- clients = Client.all(:include => :address, :limit => 10)
- clients.each do |client|
- puts client.address.postcode
- end
clients = Client.all(:include => :address, :limit => 10) clients.each do |client| puts client.address.postcode end
一次性载入post的所有分类和评论
- Post.all :include => [:category, :comments]
Post.all :include => [:category, :comments]
载入category为1的所有post和cooment及tag
- Category.find 1, :include => {:posts => [{:comments => :guest}, :tags]}
Category.find 1, :include => {:posts => [{:comments => :guest}, :tags]}
6、动态查询
- 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')
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
- 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数组
- Client.connection.select_all("SELECT * FROM clients WHERE id = '1'")
Client.connection.select_all("SELECT * FROM clients WHERE id = '1'")
9、判断记录是否存在
- #通过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?
#通过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、计算
- #求结果集条数
- Client.count(:conditons => "first_name = 'Ryan'")
- #求某个字段非空白的条数
- Client.count(:age)
- #平均值
- Client.average("orders_count")
- #求最小值
- Client.minimum("age")
- #求最大值
- Client.maximum("age")
- #求和
- 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,鼠标移入可暂停 ...
评论
发表评论
表情图标
字体颜色: 标准深红红色橙色棕色黄色绿色橄榄青色蓝色深蓝靛蓝紫色灰色白色黑色 字体大小: 标准1 (xx-small)2 (x-small)3 (small)4 (medium)5 (large)6 (x-large)7 (xx-large) 对齐: 标准居左居中居右
提示:选择您需要装饰的文字, 按上列按钮即可添加上相应的标签
您还没有登录,请登录后发表评论(快捷键 Alt+S / Ctrl+Enter)
bellstar
搜索本博客
最近访客 >>更多访客
博客分类
- 全部博客 (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 好美丽
其他分类
最近加入圈子
存档
评论排行榜
- javascript总结(一)有关框架
- javascript总结(四)ajax局部刷新与RPC
- javascript总结(二)文件组织与代码组织
- javascript总结(三)js与FLASH的交互
- javascript总结(五)获取设置元素样式与监 ...
声明:JavaEye文章版权属于作者,受法律保护。没有作者书面许可不得转载。若作者同意转载,必须以超链接形式标明文章原始出处和作者。
© 2003-2010 JavaEye.com. All rights reserved. 上海炯耐计算机软件有限公司 [ 沪ICP备05023328号 ]