Forum: Ruby on Rails how to convert the find_by_sql code to the find code

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
C42c706fab23da0b6d3cd2cfbb8db27d?d=identicon&s=25 Salil Gaikwad (salil)
on 2009-04-16 15:33
I want to convert following query into pure ruby one i.e. by using find

@us_state_sales=SalesReport.find_by_sql("SELECT z.state,
sum(s.royalty_price) as royalty_price, sum(s.units) as units FROM
sales_reports s, zip_codes z
WHERE  royalty_currency = 'USD' && z.zip_code=s.postal_code GROUP BY
z.state ORDER BY royalty_price desc LIMIT 10")


i try something like this

    @us_state_sales1=SalesReport.find(:all,  :select=>" zip_codes.state,
sum(sales_reports.royalty_price) as royalty_price,
sum(sales_reports.units) as units",
:joins=>'zip_codes',
:conditions=>["royalty_currency = 'USD' &&
zip_codes.zip_code=s.postal_code"],
:group=> 'zip_codes.state',
:order=> 'royalty_price desc',
:limit=> 10)



but it gives me the following error ......

Mysql::Error: Unknown column 'zip_codes.state' in 'field list': SELECT
zip_codes.state,sum(sales_reports.royalty_price) as royalty_price,
sum(sales_reports.units) as units FROM `sales_reports`  zip_codes WHERE
(download_date BETWEEN '2010-01-01 00:00:00' AND '2010-12-31 00:00:00'
&& artist_name in ("Coconut Records","Reeve Oliver","Weezer") &&
royalty_currency = 'USD' && zip_codes.zip_code=s.postal_code)  GROUP BY
zip_codes.state ORDER BY royalty_price desc LIMIT 10




Regards,

Salil
This topic is locked and can not be replied to.