I have a functionality that initially shows the results in HTML (a report) and then
can be exported to CSV and XLS
The idea is to reuse the results, of the query used to render the HTML, to export the same records without re-running the query again
The closest implementation is this: Storing the result in the global variable $last_consult
I have the following INDEX method in a Ruby controller
def index
begin
respond_to do |format|
format.html {
@filters = {}
@filters['email_enterprise'] = session[:enterprise_email] ;
# Add the selected filters
if (params[:f_passenger].to_s != '')
@filters['id_passenger'] = params[:f_passenger] ;
end
if (session[:role] == 2)
@filters['cost_center'] = session[:cc_name]
end
# Apply the filters and assign them to $last_consult that is used
$last_consult = InfoVoucher.where(@filters)
@info_vouchers = $last_consult.paginate(:page => params[:page], :per_page => 10)
estimate_destinations (@info_vouchers)
@cost_centers = fill_with_cost_centers(@info_vouchers)
}
format.csv {
send_data InfoVoucher.export
}
format.xls {
send_data InfoVoucher.export(col_sep: "\t")
}
The .export method is defined like this
class InfoVoucher < ActiveRecord::Base
include ActiveModel::Serializers::JSON
default_scope { order('voucher_created_at DESC') }
def attributes
instance_values
end
#Exporta a CSV o XLS
def self.export(options = {})
column_names = ["...","...","...","...","...",...]
exported_col_names = ["Solicitud", "Inicio", "Final", "Duracion", "Pasajero", "Unidades", "Recargo", "Propina", "Costo", "Centro de costo", "Origen", "Destino", "Proyecto", "Conductor", "Placas"]
CSV.generate(options) do |csv|
csv << exported_col_names
$last_consult.each do |row_export|
csv << row_export.attributes['attributes'].values_at(*column_names)
end
end
end
end
But this approach only works as long as there is no concurrent users between viewing the report and exporting it which in this case is unaceptable
I try to use a session variable to store the query result but since the result of the query can be quite it fails with this error
ActionDispatch::Cookies::CookieOverflow: ActionDispatch::Cookies::CookieOverflow
I have read about flash but don't consider it a good choice for this
Can you please point me in the right direction in how to persist the query results ,currently store in $last_consult, and make it avaible for the CSV and XLS export without using a global or session variable
Rails 4 has a bunch of cache solutions:
SQL query caching: caches the query result set for the duration of the request.
Memory caching: Limited to 32 mb. An example use is small sets, such as a list of object ids that were time-consuming to generate, e.g. the result of a complex select.
File caching: Great for huge results. Probably not what you want for your particular DB query, unless your results are huge and also you're using a RAM disk or SSD.
Memcache and dalli: an excellent fast distributed cache that's independent of your app. For your question, memcache can be a very good solution for apps that return the same results or reports to multiple users.
Terracotta Ehcache: this is enterprise and JRuby. I haven't personally used it. Looks like it good be good if you're building a serious workhorse app.
When you use any of these, you don't store the information in a global variable, nor a controller variable. Instead, you store the information by creating a unique cache key.
If your information is specific to a particular user, such as the user's most recent query, then a decent choice for the unique cache key is "#{current_user.id}-last-consult".
If your information is generic across users, such as a report that depends on your filters and not on a particular user, then a decent choice for the unique cache key is @filters.hash.
If your information is specific to a particular user, and also the the specific filters, the a decent choice for the unique cache is is "#{current_user.id}-#{@filters.hash}". This is a powerful generic way to cache user-specific information.
I have had excellent success with the Redis cache gem, which can work separately from Rails 4 caching. https://github.com/redis-store/redis-rails
I found this great article about most of the caching strategies that you mention
http://hawkins.io/2012/07/advanced_caching_part_1-caching_strategies/
After reading joelparkerhenderson answer I read this this great article about most of the caching strategies he mentioned
http://hawkins.io/2012/07/advanced_caching_part_1-caching_strategies/
I decided to use Dalli gem that depends on memcached 1.4+
And in order to configure and use Dalli I read
https://www.digitalocean.com/community/tutorials/how-to-use-memcached-with-ruby-on-rails-on-ubuntu-12-04-lts and
https://github.com/mperham/dalli/wiki/Caching-with-Rails
And this is how it ended up being implemented:
Installation/Configuration
sudo apt-get install memcached
installation can be verified running command
memcached -h
Then install the Dalli gem and configure it
gem install dalli
Add this lines to the Gemfile
# To cache query results or any other long-running-task results
gem 'dalli'
Set this lines in your config/environments/production.rb file
# Configure the cache to use the dalli gem and expire the contents after 1 hour and enable compression
config.perform_caching = true
config.cache_store = :dalli_store, 'localhost:11211', {:expires_in => 1.hour, :compress => true }
Code
In the controller I created an new method called query_info_vouchers which runs the query and stores the result in the cache by calling the Rails.cache.write method
In the index method I call the fetch to see if any cached data is available and this is only done for the CSV and XLS export format
def index
begin
add_breadcrumb "Historial de carreras", info_vouchers_path
respond_to do |format|
format.html {
query_info_vouchers
}
format.csv {
@last_consult = Rails.cache.fetch ("#{session[:passenger_key]}_last_consult") do
query_info_vouchers
end
send_data InfoVoucher.export(@last_consult)
}
format.xls {
@last_consult = Rails.cache.fetch ("#{session[:passenger_key]}_last_consult") do
query_info_vouchers
end
send_data InfoVoucher.export(@last_consult,col_sep: "\t")
}
end
rescue Exception => e
Airbrake.notify(e)
redirect_to manager_login_company_path, flash: {notice: GlobalConstants::ERROR_MESSAGES[:no_internet_conection]}
end
end
def query_info_vouchers
# Por defecto se filtran las carreras que son de la empresa
@filters = {}
@filters['email_enterprise'] = session[:enterprise_email] ;
# Add the selected filters
if (params[:f_passenger].to_s != '')
@filters['id_passenger'] = params[:f_passenger] ;
end
if (session[:role] == 2)
@filters['cost_center'] = session[:cc_name]
end
# Apply the filters and store them in the MemoryCache to make them available when exporting
@last_consult = InfoVoucher.where(@filters)
Rails.cache.write "#{session[:passenger_key]}_last_consult", @last_consult
@info_vouchers = @last_consult.paginate(:page => params[:page], :per_page => 10)
estimate_destinations (@info_vouchers)
@cost_centers = fill_with_cost_centers(@last_consult)
end
An in the model .export method
def self.export(data, options = {})
column_names = ["..","..","..","..","..","..",]
exported_col_names = ["Solicitud", "Inicio", "Final", "Duracion", "Pasajero", "Unidades", "Recargo", "Propina", "Costo", "Centro de costo", "Origen", "Destino", "Proyecto", "Conductor", "Placas"]
CSV.generate(options) do |csv|
csv << exported_col_names
data.each do |row_export|
csv << row_export.attributes['attributes'].values_at(*column_names)
end
end
end
