title: Using raw sql queries in Rails 3 application?
body: <p>I am working on migrating a legacy database into my Rails application (3.2.3). The original database comes with quite a few long sql queries for reports. For now, what I would like to do it use the sql queries in the Rails application and then one by one (when time allows) swap the sql queries to 'proper' Rails queries.</p>
  
  <p>I have a clinical model and the controller has the following code:</p>
  
  <pre><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 &lt;&gt; 0
                                                   AND c.analysiscode &lt;&gt; 213
                                                   GROUP BY c.MonthYear;)
  </code></pre>
  
  <p>However, when I run that code I get a few errors to do with the formatting.</p>
  
  <pre><code>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 &lt;&gt; 0
  ...                               ^
  /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:10: syntax error, unexpected '&gt;'
  ...yments = 0 AND c.LineBalance &lt;&gt; 0
  ...                               ^
  /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:11: syntax error, unexpected '&gt;'
  ...          AND c.analysiscode &lt;&gt; 213
  ...                               ^
  </code></pre>
  
  <p>Is there something I should be doing to the sql query before importing it into the controller? Although it's possible there is something wrong with the query (It was written quite some time ago), it does work as expected when run directly within the database. It returns an array like this:</p>
  
  <pre><code>----------------------------------------------
  |  Year      |    Month     |     Income     |
  ----------------------------------------------
  ----------------------------------------------
  |  2012      |    January   |   20,000       |
  |  2012      |    February  |   20,000       |
  |  2012      |    March     |   20,000       |
  |  2012      |    April     |   20,000       |
  ----------------------------------------------
  etc..
  </code></pre>
  
  <p>Any help, advice or general pointers would be appreciated!</p>
  
  <p>I'm reading through <a href="http://guides.rubyonrails.org/active_record_querying.html">http://guides.rubyonrails.org/active_record_querying.html</a> trying to convert the sql query to a correct Rails query.</p>
  
  <p>So far I have matched the second to last line:</p>
  
  <pre><code>AND c.analysiscode &lt;&gt; 213
  </code></pre>
  
  <p>with</p>
  
  <pre><code>@clinical_income_by_year = Clinical.where("AnalysisCode != 213")
  </code></pre>
  
  <p>baby steps!</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I've got the filtering sorted now, thanks to the Rails guide site but I'm stuck on the grouping and sum part of the sql query. I have the following so far:</p>
  
  <pre><code>@clinical_income_by_year = Clinical.where("AnalysisCode != 213 AND Payments != 0 AND LineBalance != 0").page(params[:page]).per_page(15)
  </code></pre>
  
  <p>I'm struggling to build in the following two lines of the sql query:</p>
  
  <pre><code>sum(c.LineBalance) as "Income"
  </code></pre>
  
  <p>and</p>
  
  <pre><code>GROUP BY c.MonthYear;)
  </code></pre>
  
  <p>My view code looks like this:</p>
  
  <pre><code>&lt;% @clinical_income_by_year.each do |clinical| %&gt;
    &lt;tr&gt;
      &lt;td&gt;&lt;%= clinical.TransactionDate.strftime("%Y") %&gt;&lt;/td&gt;
      &lt;td&gt;&lt;%= clinical.TransactionDate.strftime("%B") %&gt;&lt;/td&gt;
      &lt;td&gt;&lt;%= Clinical.sum(:LineBalance) %&gt;&lt;/td&gt;
    &lt;/tr&gt;    
    &lt;% end %&gt;
  &lt;/table&gt;
    &lt;%= will_paginate @clinical_income_by_year %&gt;
  </code></pre>