Ruby on Rails export to csv - maintain mysql select statement order

Question

Exporting some data from mysql to a csv file using FasterCSV. I'd like the columns in the outputted CSV to be in the same order as the select statement in my query.

Example:

rows = Data.find(
  :all,
  :select=>'name, age, height, weight'
)

headers = rows[0].attributes.keys
FasterCSV.generate do |csv|
  csv << headers
  rows.each do |r|
    csv << r.attributes.values
  end
end

CSV Output:

height,weight,name,age
74,212,bob,23
70,201,fred,24
.
.
.

I want the CSV columns in the same order as my select statement. Obviously the attributes method is not going to work. Any ideas on the best way to ensure that the columns in my csv file will be in the same order as the select statement? Got a lot of data and performance is an issue. The select statement is not static. I realize I could loop through column names within the rows.each loop but it seems kinda dirty.

Harish Shetty · Accepted Answer · 2010-03-25T07:31:40.737

1

Use the Comma gem:

class Data < ActiveRecord:Base

  comma do
    name
    age
    height
    weight
  end

  comma :height_weight do
   name
   age
   height_in_feet
   weight
 end


end

Now you can generate the CSV as follows:

Data.all(:select => 'name, age, height, weight').to_comma

Data.all(:select => 'name, age, height_in_feet, weight').to_comma(:height_weight)

Edit:

The ActiveRecord finders does not support calculated columns in the resultset, i.e.

data = Data.first(:select => 'name, age, height/12 as height_in_feet, weight')
data.height_in_feet # throws error

You can use select_extra_columns gem if you want to include the calculated columns.

edited Mar 25 '10 at 07:31

answered Mar 25 '10 at 06:05

Harish Shetty

64,083
21
152
198

Thanks, I'll look into that. I probably should have mentioned this but my select statement is dynamically generated so I'm not sure this will work. For example my select statement could be something like 'name, age, height/12 as height_in_feet, weight'. – user301410 Mar 25 '10 at 06:44
You can add multiple comma sections for your various select combination. I have amended my answer to include additional information regarding this. – Harish Shetty Mar 25 '10 at 07:32
I can't seem to reproduce the error you talk about in your edit. What version of rails are you using and what's the specific error? – rwilliams Mar 25 '10 at 15:57
In response to your edit: that code definitely works for me (using ruby 1.8.7). And my select statements are pretty dynamic and are created from other tables in the database - I'm not going to be able to define every different comma do block in the model. Unless I can use the comma block on the fly somehow I don't think it's going to work. So maybe I just have to bite the bullet and loop through the attribute values in the order I want within the rows.each loop. – user301410 Mar 25 '10 at 16:00
I have added another answer to address the dynamic aspect of your requirement. Take a look. – Harish Shetty Mar 27 '10 at 08:51

score 0 · Answer 2 · answered Mar 25 '10 at 18:01

Try this:

def export_to_csv (rows, col_names)
  col_names = col_names.split(",") if  col_names.is_a?(String)
  FasterCSV.generate do |csv|
    # header row
    csv << col_names

    # data rows
    rows.each do |row|
      csv << col_names.collect{|name| row.send(name)}
    end
  end
end

cols = 'name, age, height, weight'
rows = Data.find(:all, :select=> cols)
csv = export_to_csv(rows, cols)

Ruby on Rails export to csv - maintain mysql select statement order

2 Answers2