Rails 3: Export to CSV - Model and all its has_many children models into one file

Question

Similar to this question. I'm using Rails 3.2 so I know I don't need FasterCSV, what I am trying to do is export all my data to a CSV file. The data has a parent Model that has seven has_many Children, and may have one or more records in each has_many child. For example.

class ParentModel < ActiveRecord::Base

has_many :child_1

has_many :child_2

has_many :child_3

Then

class Child1 < ActiveRecord::Base

belongs_to :parent_model

class Child2 < ActiveRecord::Base

belongs_to :parent_model

class Child3 < ActiveRecord::Base

belongs_to :parent_model

So I have a lot of ParentModel records, and there may be different numbers of Child1, Child2 and Child3 records for each ParentModel record. How can I dynamically figure out the first line of the CSV, and then populate the CSV, inserting NULLs if the ParentModel only happens to have one record of a Child? Is there an efficient way to do this, or does it involve a lot of loops?

For example, say in the whole database, the most amount of Child1 records a ParentModel has is two, there is only one Child2 record maximum for any ParentModel, and there are three Child3 records. So the first line of the CSV would be:

ParentModel.field1, ParentModel.field1, Child1.field1, Child1.field2, Child1(record#2).field1, Child1(record#2).field2, Child2.field1, Child2.field2,Child3.field1, Child3.field2, Child3(record#2).field1, Child3(record#2).field2, Child3(record#3).field1, Child3(record#3).field2

But then another Child1 is added to a ParentModel, so the next time you do the export, it would figure that out, and then the first line of the CSV would look like this:

ParentModel.field1, ParentModel.field1, Child1.field1, Child1.field2, Child1(record#2).field1, Child1(record#2).field2,Child1(record#3).field1, Child1(record#3).field2, Child2.field1, Child2.field2,Child3.field1, Child3.field2, Child3(record#2).field1, Child3(record#2).field2, Child3(record#3).field1, Child3(record#3).field2

Or.... after writing this out, I'm wondering if I am over-complicating this and there is a smooth 'rails way' to do this?

Thanks!

Answer 1

Basically, I had to figure out what the maximum number of children the parent model has. Then as it exports a record on each row of the CSV, it fills the columns for the child model with a - if it doesn't have as many children as the record with the most number. (I know, a little confusing, right?)

In my case, I have Parent model that can have multiple Child records.

class Parent < ActiveRecord::Base
  has_many :childs
...

class Child < ActiveRecord::Base
  belongs_to :parents
...

In the ParentsController, I find the maximum number of childs any one parent has, and grab the column names for the header of the CSV.

class ParentsController < ApplicationController

def download    

@parents = Parent.all
# find maximum number of children any parent has. Make sure to convert to integer.
@max_children = Child.select('parent_id, COUNT(parent_id)').group(:parent_id).order('COUNT(parent_id) DESC').first.count.to_i

@parent_columns = Parent.column_names
@child_columns = Child.column_names

@streaming = true
@filename = "export.csv"

respond_to do |format|
  format.csv
end

Then in the download.csv.csvbuilder I build the CSV file with a header of the column names, and headers for the maximum number of children a parent might have from the query.

header = Array.new
@parent_columns.each do |column_name| 
  header << column_name.humanize 
end 

if @child_columns
    i = 0
    while i < @max_children do 
      @child_columns.each do |column_name| 
        header << "Child #{i+1} " + column_name.humanize 
      end
      i += 1
    end
end

csv << header.flatten

@parents.each do |parent|

  parent_record = Array.new

  parent_record << parent.attributes.values_at(*@parent_columns)

## NOTE: Here is the answer

if @child_columns
  if parent.childs.first.nil?
    i = 0
    while i < @max_children do 
        @child_columns.each do |column_name|
          parent_record << "-"
        end
      i += 1
    end
  elsif !parent.childs.first.nil?
    how_many_blanks = @max_children - parent.childs.length
    parent.childs.each do |child| 
      @child_columns.each do |child_column|
        parent_record << child.attributes.values_at(*child_column)
      end
    end
    i = 0
    while i < how_many_blanks do 
        @child_columns.each do |column_name|
          parent_record << "-"
        end
      i += 1
    end
  end
end

  csv << parent_record.flatten

end 

I hope this helps!