Rails Rails SQL 查询语句不会立即执行?

raja · 2015年12月09日 · 最后由 rubysm 回复于 2018年03月15日 · 2850 次阅读

遇到一个奇怪的问题,我有一个task modelvideo model,一对多的关系,现在是想在软删除task的时候,获取它所有的video,但是由于会将相应的videotask_id置为空,导致获取到的视频结果也是空的。

函数代码如下

def destroy_tasks ids
  not_delete_ids = Task.ids
  correct_ids = ids & not_delete_ids
  correct_ids.sort!
  videos = Video.where(:task_id => correct_ids)
  names = Task.where(:id => correct_ids).pluck(:name)

  destroy_params = Array.new
  names.each do |name|
    temp = Hash.new
    t = Time.now
    temp[:name] = "#{name}-#{t.to_i}"
    temp[:delete_at] = t
    destroy_params.push temp
  end

  Task.update(correct_ids, destroy_params)
  Video.where(:task_id => correct_ids).update_all(:task_id => nil)
  respond_to do |format|
    format.html { redirect_to tasks_url, notice: "#{I18n.t('notice.destroy', model: Task.model_name.human)}"  }
    format.json { render :destroy_tasks, locals: {ids: correct_ids, action: 'destroy', videos: videos }}
  end
end

看日志发现,获取video的sql语句是在最后执行的,尽管它在程序中的语句十分靠前

Started POST "/tasks/handle" for 127.0.0.1 at 2015-12-09 15:53:18 +0800
Processing by TasksController#handle_tasks as JSON
  Parameters: {"utf8"=>"✓", "tasks"=>"113,", "next_action"=>"destroy"}
  User Load (0.5ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 1  ORDER BY `users`.`id` ASC LIMIT 1
   (0.5ms)  SELECT `tasks`.`id` FROM `tasks` WHERE (delete_at IS NULL)
   (0.4ms)  SELECT `tasks`.`name` FROM `tasks` WHERE (delete_at IS NULL) AND `tasks`.`id` = 113
  Task Load (0.5ms)  SELECT  `tasks`.* FROM `tasks` WHERE (delete_at IS NULL) AND `tasks`.`id` = 113 LIMIT 1
   (0.6ms)  BEGIN
  Task Exists (0.6ms)  SELECT  1 AS one FROM `tasks` WHERE (`tasks`.`name` = BINARY '测试任务51-1449647598' AND `tasks`.`id` != 113) LIMIT 1
  SQL (0.5ms)  UPDATE `tasks` SET `name` = '测试任务51-1449647598', `delete_at` = '2015-12-09 15:53:18', `updated_at` = '2015-12-09 15:53:18' WHERE `tasks`.`id` = 113
   (65.7ms)  COMMIT
  SQL (47.4ms)  UPDATE `videos` SET `videos`.`task_id` = NULL WHERE (delete_at IS NULL) AND `videos`.`task_id` = 113
  Video Load (0.5ms)  SELECT `videos`.* FROM `videos` WHERE (delete_at IS NULL) AND `videos`.`task_id` = 113
  Rendered tasks/destroy_tasks.json.jbuilder (1.8ms)
Completed 200 OK in 140ms (Views: 4.4ms | ActiveRecord: 117.2ms)

Video.where(:task_id => correct_ids).update_all(:task_id => nil)语句放在函数最下面就没问题。

似乎是因为ruby变量只有在第一次使用的时候才会确定它的值?依稀有这个概念,但是不太确定。

在rails console下面写了一个test函数测试,能获取正确的videos,然而在删除第一个p videos后,只会得到空结果。

def test id
  task = Task.find id
  videos = task.videos
  p videos
  t = Time.now
  task.update(:delete_at => t, :name => "#{task.name}-#{t.to_i}")
  Video.where(:task_id => id).update_all(:task_id => nil)
  p videos
end

补充

google到一篇文章提到了这个,还有这篇

共收到 8 条回复

没有 “似乎是因为ruby变量只有在第一次使用的时候才会确定它的值?依稀有这个概念,但是不太确定。” 这回事。 videos = Video.where(:task_id => correct_ids) 返回一个 ActiveRecord_Relation 对象,它是lzay的,在使用的时候才执行查询数据库的动作。

可以用videos.update_all ...

where语句,包括limit order这些语句,都是在「构造查询」而非「查询」。 只有在最后使用的时候才会去真的查询这些数据,比如当你用first to_a each map count的时候,才会真正地向服务器发出请求。

data = Model.where(...) # SELECT * FROM model WHERE ...
data = data.order(...)  # SELECT * FROM model WHERE ... ORDER BY ...
data = data.where(...)  # SELECT * FROM model WHERE ... AND ... ORDER BY ...
data = data.select(...) # SELECT ... FROM model WHERE ... AND ... ORDER BY ...
data = data.limit(...)  # SELECT ... FROM model WHERE ... AND ... ORDER BY ... LIMIT ...
p data                  # <-- 这里才执行了查询

#1楼 @nowherekai 明白了,谢谢!

#3楼 @nine 好的,因为videos变量是昨天更新时才加上去的,没注意到😅

写的有点怪怪的,感觉传过来的id没必要再&一次

def destroy_tasks ids
  videos = Video.where(task_id: ids)
  result = { ids: ids, action: 'destroy', videos: videos }

  time = Time.now
  destroy_params = Task.where(id: ids).pluck(:name).map { |name| {name: "#{name}-#{time.to_i}", delete_at: time} }

  Task.update(ids, destroy_params) and Video.where(task_id: ids).update_all(task_id: nil)
  respond_to do |format|
    format.html { redirect_to tasks_url, notice: "#{I18n.t('notice.destroy', model: Task.model_name.human)}" }
    format.json { render :destroy_tasks, locals: result }
  end
end
rubysm ActiveRecord MySQL 锁的问题 中提及了此贴 03月15日 18:33
nowherekai 回复

请问 lazy 可以关掉么?谢谢大侠。

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册