• 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)   
    4. Model.last   
    5. Model.last(options)   
    6. 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")   
    3. #!方法,没有记录时抛出ActiveRecord::RecordNotFound异常
    4. Client.find_by_name!("Ryan")   
    5. #查询多个字段
    6. Client.find_by_name_and_locked("Ryan", true)   
    7. #查询不到时就创建并保存
    8. Client.find_or_create_by_name(params[:name])   
    9. #查询不到时创建一个实例,但不保存
    10. 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)   
    3. Client.exists?(1, 2, 3)   
    4. #or
    5. Client.exists?([1,2,3])   
    6. #通过其他条件来查询
    7. Client.exists?(:conditions => "first_name = 'Ryan'")   
    8. #没有参数时,则:表是空的 ? false : true
    9. 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'")   
    3. #求某个字段非空白的条数
    4. Client.count(:age)   
    5. #平均值
    6. Client.average("orders_count")   
    7. #求最小值
    8. Client.minimum("age")   
    9. #求最大值
    10. Client.maximum("age")   
    11. #求和
    12. 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的博客

    bellstar

    搜索本博客
    最近访客 >>更多访客

    Richmond1979的博客

    Richmond1979

    oleiowang的博客

    oleiowang

    maleo的博客

    maleo

    changcheng3920的博客

    changcheng3920

    博客分类
    我的相册

    sinatra项目练习
    共 18 张

    我的留言簿 >>更多留言
    • 你填的中专学历有没有进大公司??我想进大公司,但是我想知道大公司承认成人高考不
      -- by 好美丽
    • 我是高中毕业的,对自己的职业前景很是迷茫啊,会jdbc,会ssh,感觉太少了。 路 ...
      -- by 好美丽
    其他分类
    最近加入圈子
    存档
    评论排行榜
    • Rss
    • Rss_google

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

  • 相关阅读:
    【python】学习笔记10-ddt数据驱动
    【python】学习笔记10-装饰器
    【Python】学习笔记8-多线程多进程
    【Python】学习笔记7-异常处理try。。except .. as e ....else
    【Python】学习笔记6-补充Flask模块:登录接口,mysql数据库、存redis-sesson、存浏览器cookie
    【Python】学习笔记6-创建Excel:xlwt,读取Excel:xlrd ,修改Excel:xlutils
    【Python】学习笔记6-网络编程urllib,request,请求rul
    【Python】学习笔记5-利用flask来mock接口
    【Python】学习笔记5-操作redis数据库redis
    【Python】学习笔记5-模块pymysql操作mysql数据库
  • 原文地址:https://www.cnblogs.com/lexus/p/1948320.html
Copyright © 2020-2023  润新知