0

So I am pulling my hair over this issue / gotcha. Basically I used find_by_sql to fetch data from my database. I did this because the query has lots of columns and table joins and I think using ActiveRecord and associations will slow it down.

I managed to pull the data and now I wanted to modify returned values. I did this by looping through the result ,for example.

a = Project.find_by_sql("SELECT mycolumn, mycolumn2 FROM my_table").each do |project|
  project['mycolumn'] = project['mycolumn'].split('_').first
end

What I found out is that project['mycolumn'] was not changed at all.

So my question:

Does find_by_sql return an array Hashes? Is it possible to modify the value of one of the attributes of hash as stated above?

Here is the code : http://pastie.org/4213454 . If you can have a look at summarize_roles2() that's where the action is taking place.

Thank you. Im using Rails 2.1.1 and Ruby 1.8. I can't really upgrade because of legacy codes.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
r2b2
  • 1,255
  • 5
  • 13
  • 34
  • Don't *think* that something will be slower/faster, benchmark both methods and *know* which is faster (and if it's fast enough to be worth it). – Andrew Marshall Jul 07 '12 at 04:41

2 Answers2

0

Just change the method above to access the values, print value of project and you can clearly check the object property.

The results will be returned as an array with columns requested encapsulated as attributes of the model you call this method from.If you call Product.find_by_sql then the results will be returned in a Product object with the attributes you specified in the SQL query.
If you call a complicated SQL query which spans multiple tables the columns specified by the SELECT will be attributes of the model, whether or not they are columns of the corresponding table.

Post.find_by_sql "SELECT p.title, c.author FROM posts p, comments c WHERE p.id = c.post_id"
> [#<Post:0x36bff9c @attributes={"title"=>"Ruby Meetup", "first_name"=>"Quentin"}>, ...]

Source: http://api.rubyonrails.org/v2.3.8/

Bijendra
  • 9,467
  • 8
  • 39
  • 66
0

Have you tried 

a = Project.find_by_sql("SELECT mycolumn, mycolumn2 FROM my_table").each do |project|
  project['mycolumn'] = project['mycolumn'].split('_').first
  project.save
end
Wawa Loo
  • 2,266
  • 18
  • 15