I have a fairly large query (the result takes along time to return)
that I need to export to CSV after it is displayed with a view.
Basically a link at the bottom of the page, “Export to CSV”. I’ve
found the rails wiki solution: Peak Obsession . The only
problem is that I would like to have this in it’s own controller and
I’m wondering how I should keep a handle on the ActiveRecord object
that has already been created from the query, I would also like to keep
this as DRY as possible and not have to regenerate the query. Should I
store the ActiveRecord object in the session variable or ???
Storing AR objects in the session will work, but there is no guarentee
that
the data will persist. The only thing you can count on with AR objects
is
the object ID (primary key, whatever it is). In this case, that if you
run
the query, save the AR object to the session, and then access elsewhere,
given a large enough dataset, ActiveRecord will most likely have
forgotten
the results and will rerun the query when you access it again.
In this case, just pass the object id to the CSV controller. That way
you
know the query will only be generated and run once.
Storing AR objects in the session will work, but there is no guarentee
that the data will persist. The only thing you can count on with AR
objects is the object ID (primary key, whatever it is). In this case,
that if you run the query, save the AR object to the session, and then
access elsewhere, given a large enough dataset, ActiveRecord will most
likely have forgotten the results and will rerun the query when you
access it again.
In this case, just pass the object id to the CSV controller. That way
you know the query will only be generated and run once.
Are you only running this query for a CSV export or are you running this
query for a view, and then giving the user a link to
download a CSV export?
If the later you’ll need to rerun your query most likely since CSV
exports aren’t paged, but results shown via a web page usually
are (and should be).
Most databases are smart though, and they will most likely cache the
query and it’s resulset. You should look into performance of
running the exact same query twice with different offsets (one too mimic
web page query, and the second to mimic export to CSV query).
How big is this resulset?
Zach
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
Since this is a large query I don’t think I’d store it in the session.
What happens if the user doesn’t want to download the csv? Then you’ve
stored it for no reason. If it’s somewhat static data you could cache
both the rhtml view and also a csv version. Then have those expire when
the data is updated.
A simpler solution would be to just paginate the results for the user,
and then do the full query for csv creation. Caching could work in this
scenario as well, but it all depends on your needs. If pagination is
out of the question, and so is caching, then create the csv file just
prior to rendering the view. This way you won’t have to generate it
twice at least.
Storing AR objects in the session will work, but there is no guarentee that
the data will persist. The only thing you can count on with AR objects is
the object ID (primary key, whatever it is). In this case, that if you run
the query, save the AR object to the session, and then access elsewhere,
given a large enough dataset, ActiveRecord will most likely have forgotten
the results and will rerun the query when you access it again.
I wasn’t aware that AR cached result data?
My suggestion would be to write the csv file to disk, preferably with
something like Zlib to compress it also, then use send_data/send_file
depending on the size to send the file to the browser. On subsequent
requests only generate the csv file if the data has changed.
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.