ruby-on-rails - 是否可以在 Rails 中缓存自定义 sql 查询？

Question

在 Rails 4 应用程序的 home_controller 中，我执行自定义 SQL 查询并将结果保存到实例变量

@studentscoring = ActiveRecord::Base.connection.execute sql_string_student

然后，在配置开发中将缓存设置为 true config.action_controller.perform_caching = true 并重新启动应用程序后，在 View 中的相关变量周围设置缓存。

  <% cache @studentscoring do%>
    <% for lawyer in @studentscoring  %>
    <div class="span2">
      <div class="row">
        <%=  tiny_gravatar_for lawyer['name'], lawyer['email'] %>

      </div>
      ...... #code ommitted
    </div>

    <% end %>
    <% end %>

刷新浏览器三次显示查询运行了三次，并且最后一次运行查询实际上比第一次运行花费了 0.7 毫秒，所以我假设缓存不起作用或者我没有正确执行:)。你能告诉我我做错了什么吗？

从任何标准来看都不是专家，我不明白如何使用 <% cache ... do %> 语法从 View 触发缓存，因为在加载 View 时 Controller 还没有查询已经运行，因此告诉 Rails 使用缓存副本为时已晚？

来自服务器日志...

第一

 (1.1ms)  with cte_scoring as (
 select
 users.id, users.name, users.email,
 (select Coalesce(sum(value),0) from answer_votes where (answer_votes.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +
 (select Coalesce(sum(value),0) from best_answers where (best_answers.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +
 (select Coalesce(sum(value),0) from contributions where (contributions.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) total_score
 from
 users
 where
 users.student = 'true') 

select id,
 name,
 email,
 total_score
from cte_scoring
order by total_score desc
limit 5 

第三

  (1.8ms)  with cte_scoring as (
 select
 users.id, users.name, users.email,
 (select Coalesce(sum(value),0) from answer_votes where (answer_votes.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +
 (select Coalesce(sum(value),0) from best_answers where (best_answers.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) +
 (select Coalesce(sum(value),0) from contributions where (contributions.user_id = users.id) AND (created_at >= Current_Date - interval '7 day')) total_score
 from
 users
 where
 users.student = 'true') 

select id,
 name,
 email,
 total_score
from cte_scoring
order by total_score desc
limit 5 

更新

日志显示它正在读取一个片段(在运行上述查询之后)，那么为什么查询会有不同的时间并且后面的查询会更慢？我本以为如果有片段可供读取，查询根本不会运行。

Read fragment views/id/75/name/Retarded Student/email/retarstudent@gmail.com/total_score/0/id/83/name/Jim Beam/email/jimbean@gmail.com/total_score/0/id/79/name/Weird Student/email/weirdstudent@gmail.com/total_score/0/id/80/name/VegetableSTudent/email/veggiestudent@gmail.com/total_score/0/c9638e467bfd0fbf5b619ab411182256 (0.3ms)

Answer 1

将查询结果缓存在 Controller 中。您可以在一次调用中读取或写回缓存(即，如果缓存中尚不存在数据，则设置该数据)

def index
  @studentscoring = Rails.cache.fetch("your_cache_key", :expires_in => 5.minutes) do
    ActiveRecord::Base.connection.select_rows(sql_string_student)
  end
end

因此上面将首先检查缓存中的“your_cache_key”，如果数据存在，将从缓存中返回它。如果它不存在，则该 block 将执行并将其设置在缓存中