Using raw sql queries in a Rails 3 application?

Question

I am working on porting an outdated database to my Rails application (3.2.3). The source database contains quite a few sql queries for reports. At the moment, I would like to do this using sql queries in a Rails application, and then, one by one (when time allows) to change sql queries to the "correct" Rails queries.

I have a clinical model, and the controller has the following code:

@clinical_income_by_year = Clinical.find_all_by_sql(SELECT date_format(c.transactiondate,'%Y') as Year, date_format(c.transactiondate,'%b') as Month, sum(c.LineBalance) as "Income" FROM clinical c WHERE c.Payments = 0 AND c.LineBalance <> 0 AND c.analysiscode <> 213 GROUP BY c.MonthYear;) 

However, when I run this code, I get some formatting errors.

 Started GET "/clinicals" for 127.0.0.1 at 2012-04-29 18:00:45 +0100 SyntaxError (/Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:6: syntax error, unexpected tIDENTIFIER, expecting ')' ...rmat(c.transactiondate,'%Y') as Year, ... ^ /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:7: syntax error, unexpected tIDENTIFIER, expecting keyword_end ...rmat(c.transactiondate,'%b') as Month, ... ^ /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:8: syntax error, unexpected tIDENTIFIER, expecting keyword_end ... sum(c.LineBalance) as "Income" ... ^ /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:10: syntax error, unexpected tCONSTANT, expecting keyword_end ... WHERE c.Payments = 0 AND c.LineBalance <> 0 ... ^ /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:10: syntax error, unexpected '>' ...yments = 0 AND c.LineBalance <> 0 ... ^ /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:11: syntax error, unexpected '>' ... AND c.analysiscode <> 213 ... ^ 

Is there something I have to do with the sql query before importing it into the controller? Although it is possible that something is wrong with the request (it was written a long time ago), it works as expected when launched directly in the database. It returns an array like this:

 ---------------------------------------------- | Year | Month | Income | ---------------------------------------------- ---------------------------------------------- | 2012 | January | 20,000 | | 2012 | February | 20,000 | | 2012 | March | 20,000 | | 2012 | April | 20,000 | ---------------------------------------------- etc.. 

Any help, tips or general pointers would be appreciated!

I am reading http://guides.rubyonrails.org/active_record_querying.html trying to convert an SQL query into a valid Rails query.

So far I have matched the second in the last line:

 AND c.analysiscode <> 213 

from

 @clinical_income_by_year = Clinical.where("AnalysisCode != 213") 

baby steps!

UPDATE

I have the filtering sorted now thanks to the Rails site, but I am stuck in a grouping and summarizing part of the SQL query. I have the following:

 @clinical_income_by_year = Clinical.where("AnalysisCode != 213 AND Payments != 0 AND LineBalance != 0").page(params[:page]).per_page(15) 

I am trying to build in the following two lines of sql query:

 sum(c.LineBalance) as "Income" 

and

 GROUP BY c.MonthYear;) 

My code is as follows:

 <% @clinical_income_by_year.each do |clinical| %> <tr> <td><%= clinical.TransactionDate.strftime("%Y") %></td> <td><%= clinical.TransactionDate.strftime("%B") %></td> <td><%= Clinical.sum(:LineBalance) %></td> </tr> <% end %> </table> <%= will_paginate @clinical_income_by_year %> 

Answer 1

The Ruby analyzer does not understand SQL, you need to use the line:

 @clinical_income_by_year = Clinical.find_by_sql(%q{ ... }) 

I would recommend using %q or %q (if you need interpolation) for this, so you don't need to worry about inline quotes. You should also transfer this to the class method in the model so that your controllers do not worry about things that are not their business, it will also give you easy access to connection.quote and friends so that you can correctly use string interpolation:

 find_by_sql(%Q{ select ... from ... where x = #{connection.quote(some_string)} }) 

Also, the semicolon in your SQL:

 GROUP BY c.MonthYear;}) 

not required. Some databases will skip this, but you should still get rid of it.

Depending on your database, identifiers (table names, column names ...) should be case insensitive (unless some hated person quoted them when they were created) so you can use lowercase column names to make things fit better in Rails.

Also note that some databases will not like this GROUP BY, since you have columns in SELECT that are not aggregated or grouped, so there is ambiguity about which c.transactiondate used for each group.

---

A more "Railsy" version of your request would look something like this:

 @c = Clinical.select(%q{date_format(transactiondate, '%Y') as year, date_format(transactiondate, '%b') as month, sum(LineBalance) as income}) .where(:payments => 0) .where('linebalance <> ?', 0) .where('analysiscode <> ?', 213) .group(:monthyear) 

Then you can do things like this:

 @c.each do |c| puts c.year puts c.month puts c.income end 

to access the results. You can also simplify things a bit by tapping the date turning into Ruby:

 @c = Clinical.select(%q{c.transactiondate, sum(c.LineBalance) as income}) .where(:payments => 0) .where('linebalance <> ?', 0) .where('analysiscode <> ?', 213) .group(:monthyear) 

Then push c.transactiondate into Ruby, rather than c.year and c.month .