CODEWITHSUNDEEP
ruby-on-railscsv
Mark Locklear
Mark Locklear

Reputation: 5325

Rails and exporting a console query to csv

I have the following query I am running...

require 'csv'
require File.dirname(__FILE__) + '/config/environment.rb'

file = "#{Rails.root}/public/data.csv"
registrations = OnlineCourseRegistration.where(course_class_id: 681).where(status: "Completed").where("score >= ?", "80").where("exam_completed_at BETWEEN ? AND ?", 3.years.ago, Date.today)
 
CSV.open( file, 'w' ) do |writer|
  writer << registrations.first.attributes.map { |a,v| a }
  registrations.each do |s|
    if s.user_id
        writer << User.find(s.user_id).email
    end
    writer << s.attributes.map { |a,v| v }
  end
end

This is failing on the line writer << User.find(s.user_id).email with the error:

`<<': undefined method `map' for "[email protected]":String (NoMethodError)`

Basically, I just want to add a column with the users email address in it.

Update

Here is the current output w/out the email field

id  cart_id user_id course_class_id created_at  updated_at  exam_attempts   exam_completed_at   evaluation_completed_at status  score   add_extension   retest_cart_id  retest_purchased_at
11990   10278   6073    681 2014-10-30 20:34:18 UTC 2014-12-17 14:48:39 UTC 2   2014-12-17 03:16:44 UTC 2014-12-17 14:48:39 UTC Completed   90  FALSE       
11931   10178   6023    681 2014-09-02 22:35:08 UTC 2015-02-24 03:58:03 UTC 1   2015-02-24 03:56:12 UTC 2015-02-24 03:58:03 UTC Completed   80  FALSE       
12015   10316   6089    681 2014-11-15 14:31:05 UTC 2014-11-18 20:14:13 UTC 1   2014-11-18 20:11:46 UTC 2014-11-18 20:14:13 UTC Completed   82  FALSE       
12044   10358   6103    681 2014-12-03 15:56:39 UTC 2014-12-06 23:05:18 UTC 2   2014-12-06 23:02:13 UTC 2014-12-06 23:05:18 UTC Completed   94  FALSE

So I would like to append an email field to each of the colums above.

Upvotes: 2

Views: 7005

Answers (4)

max
max

Reputation: 102222

First make sure you have defined an association and use it:

class OnlineCourseRegistration
  belongs_to :user
end

class User
  has_many :online_course_registrations
end

One major problem with your code is that:

User.find(s.user_id)

Will cause one additional DB query per row returned from online_course_registrations which is really slow.

So you want to join the rows. 

registrations = OnlineCourseRegistration.where(course_class_id: 681)
                                        .joins(:user)
                                        .where(status: "Completed")
                                        .where("score >= ?", "80")
                                        .where(exam_completed_at: (3.years.ago.. Date.today))

You could get the attributes plus the user email by doing:

registrations.map do |r|
  r.attributes.values << r.user.email
end

attributes returns a hash. Hash#values returns the values as an array. We then push the email on the end of the array. Easy peasy.

But using .pluck to get the values straight from the db is far more effective in the first place:

columns = OnlineCourseRegistration.column_names.map {|c| "#{OnlineCourseRegistration.table_name}.#{c}" }.push('users.email')

registrations = OnlineCourseRegistration.where(course_class_id: 681)
                                        .joins(:user)
                                        .where(status: "Completed")
                                        .where("score >= ?", "80")
                                        .where(exam_completed_at: (3.years.ago.. Date.today))
registrations.pluck(*columns)

To output to csv you would do:

CSV.open( file, 'w' ) do |csv|
  registrations.each {|r| csv << r }
end

Upvotes: 1

Mark Locklear
Mark Locklear

Reputation: 5325

This is pretty ghetto. But full disclosure, it's an adhoc report a customer is requesting and it does what they need it to. I just concatinated [User.find(s.user_id).email] onto the writer statement. Looks like simply wrapping brackets around it is enough to do the trick. Here is the full code...

require 'csv'
require File.dirname(__FILE__) + '/config/environment.rb'

file = "#{Rails.root}/public/data.csv"

registrations = OnlineCourseRegistration.where(course_class_id: 681).where(status: "Completed").where("score >= ?", "80").where("exam_completed_at BETWEEN ? AND ?", 3.years.ago, Date.today)

CSV.open( file, 'w' ) do |writer|
  writer << registrations.first.attributes.map { |a,v| a }
  registrations.each do |s|

    writer << s.attributes.map { |a,v| v } + [User.find(s.user_id).email]
  end
end

Upvotes: 8

jvillian
jvillian

Reputation: 20263

I don't use CSV, but I infer that writer << calls map on whatever is to the right of <<. A string doesn't respond to map. And, User.find(s.user_id).email is a string. So you get the error.

I suppose you could build an array that includes the email address and then call: 

writer << array_with_email_in_correct_location

on it. But that seems weird because I think you'd end up with a row with just an email address. Unless that's what you want.

With out seeing some sample data and a desired result, it's hard to say.

Upvotes: 1

fabOnReact
fabOnReact

Reputation: 5942

either registrations.first.attributes or s are string.

String class does not have a :map method

if you need more help add more info

Upvotes: 0

Related Questions