Is it possible to cache custom sql queries in Rails?

Question

In the home_controller of my Rails 4 app, I perform a custom sql query and save the results to an instance variable

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

I then, after setting caching to true in config development config.action_controller.perform_caching = true and restarting the application, set up caching around the relevant variable in the 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 %>

Refreshing the browser three times shows that the query is run three separate times and the last run of the query actually takes .7ms longer than the first, so I'm assuming caching is not working or I'm not doing it correctly :). Can you tell me what I'm doing wrong?

Not being an expert by any standards, I don't understand how caching can be triggered from the view with the <% cache ... do %> syntax, since by the time the view is loading haven't the controller queries already been run, therefore it's too late to tell Rails to use a cached copy?

from the server logs...

First

 (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 

3rd

  (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 

Update

The logs show that it is reading a fragment (after the queries above are run), so why would the queries have different times and the later query be slower? I would have thought the queries wouldn't be run at all if there was a fragment to read from.

Read fragment views/id/75/name/Retarded Student/email/[email protected]/total_score/0/id/83/name/Jim Beam/email/[email protected]/total_score/0/id/79/name/Weird Student/email/[email protected]/total_score/0/id/80/name/VegetableSTudent/email/[email protected]/total_score/0/c9638e467bfd0fbf5b619ab411182256 (0.3ms)

Answer 1

Cache the query results in your controller. You can read or write back to the cache in one call (that is, set the data in the cache if it does not already exist)

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

So the above will first check the cache for "your_cache_key" and if the data exists will return it from the cache. If it does not exist than the block will execute and it will be set in the cache