See morw...

Thursday, October 27, 2011

How to extract values from find_by_sql or join query in Ruby on Rails?

If you are a developer or designer on the ROR , then you might be facing a problem to extract values from rails find_by_sql method. 
Usually I'm using this method to run complex sql queries. there can be many reasons. my main reasons to use this method are


1. Avoid multiple hits to Database
2. Less processing
3. Super fast
4. Reduced data set


Lets say you are having multiple models and you needs to join those table to get some results. 
Shop , Book , Author , Tag , Customer , Stock
you can use the rail's traditional join or find_by_sql method. both are same and you can choose whatever you need.


method 1 : 
Shop.joins(":books => [ { :author => :tag } , :customer ]").where("xxxxxxxxx").select("xxxxx").group("xxxxxxxx")


method 2 : 
Shop.find_by_sql("select * from shops s outer join books b on b.shop_id = s.id xxxxxxx ")
Both above queries will return the basic object as a results. for example it will returns the Shop active record object. you wont see the results from the select clause. so here is the way to get that. super simple. 


shops.each do |shop|
    shop.attributes().each do |attr|
       column = attr[0]
       value = attr[1]
   end
end
Cheers!!!  Happy coding.......

No comments: