sql - 在 Rails 3 应用程序中使用原始 sql 查询？

Question

我正在将旧数据库迁移到我的 Rails 应用程序 (3.2.3) 中。原始数据库带有相当多的用于报告的长 sql 查询。现在，我想做的是使用 Rails 应用程序中的 sql 查询，然后一个一个(在时间允许的情况下)将 sql 查询交换为“正确的”Rails 查询。

我有一个临床模型， Controller 具有以下代码:

 @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;)

但是，当我运行该代码时，我会遇到一些与格式有关的错误。

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
...                               ^

在将 sql 查询导入 Controller 之前，我应该对它做些什么吗？尽管查询可能有问题(它是很久以前写的)，但当直接在数据库中运行时，它确实按预期工作。它返回一个这样的数组:

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

任何帮助、建议或一般指示将不胜感激!

我正在阅读 http://guides.rubyonrails.org/active_record_querying.html试图将 sql 查询转换为正确的 Rails 查询。

到目前为止，我已经匹配了倒数第二行:

AND c.analysiscode <> 213

和

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

宝贝步骤!

更新

多亏了 Rails 指南站点，我现在已经对过滤进行了排序，但我仍然停留在 sql 查询的分组和求和部分。到目前为止，我有以下几点:

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

我正在努力构建以下两行 sql 查询:

sum(c.LineBalance) as "Income"

和

GROUP BY c.MonthYear;)

我的 View 代码如下所示:

<% @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

Ruby 解析器不理解 SQL，您需要使用字符串:

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

我建议使用 %q或 %Q (如果您需要插值)为此，您不必担心嵌入引号。您还应该将其移动到模型中的类方法中，以防止您的 Controller 担心与他们无关的事情，这也将使您轻松访问 connection.quote 和 friend ，以便您可以正确使用字符串插值:

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

此外，您的 SQL 中的分号:

GROUP BY c.MonthYear;})

没有必要。有些数据库会让它通过，但无论如何你应该摆脱它。

根据您的数据库，标识符(表名、列名...)应该不区分大小写(除非某些可恶的人在创建它们时引用了它们)，因此您可以使用小写的列名来使事情适合导轨更好。

另请注意，某些数据库不喜欢该 GROUP BY，因为您的 SELECT 列中有未聚合或分组的列，因此对于哪个 c.transactiondate 存在歧义。用于每个组。

查询的更“Railsy”版本将如下所示:

@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)

然后你可以做这样的事情:

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

访问结果。您还可以通过将日期修改插入 Ruby 来简化一点:

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

然后拉开 c.transactiondate在 Ruby 中而不是调用 c.year和 c.month .