在Rails 3应用程序中使用原始SQL查询?

前端之家收集整理的这篇文章主要介绍了在Rails 3应用程序中使用原始SQL查询?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我正在努力将遗留数据库迁移到我的Rails应用程序(3.2.3).原始数据库附带了很多用于报告的长SQL查询.现在,我想做的是在Rails应用程序中使用SQL查询,然后逐个(当时间允许时)将SQL查询交换为“正确的”Rails查询.

我有一个临床模型,控制器有以下代码

  1. @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"
  2. FROM clinical c
  3. WHERE c.Payments = 0 AND c.LineBalance <> 0
  4. AND c.analysiscode <> 213
  5. GROUP BY c.MonthYear;)

但是,当我运行该代码时,我得到一些与格式有关的错误.

  1. Started GET "/clinicals" for 127.0.0.1 at 2012-04-29 18:00:45 +0100
  2.  
  3. SyntaxError (/Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:6: Syntax error,unexpected tIDENTIFIER,expecting ')'
  4. ...rmat(c.transactiondate,... ^
  5. /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:7: Syntax error,expecting keyword_end
  6. ...rmat(c.transactiondate,... ^
  7. /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:8: Syntax error,expecting keyword_end
  8. ... sum(c.LineBalance) as "Income"
  9. ... ^
  10. /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:10: Syntax error,unexpected tCONSTANT,expecting keyword_end
  11. ... WHERE c.Payments = 0 AND c.LineBalance <> 0
  12. ... ^
  13. /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:10: Syntax error,unexpected '>'
  14. ...yments = 0 AND c.LineBalance <> 0
  15. ... ^
  16. /Users/dannymcclelland/Projects/premvet/app/controllers/clinicals_controller.rb:11: Syntax error,unexpected '>'
  17. ... AND c.analysiscode <> 213
  18. ... ^

在将SQL查询导入控制器之前,我应该对SQL查询做些什么吗?尽管查询可能有问题(它是在很久以前编写的),但它直接在数据库中运行时确实可以正常工作.它返回一个这样的数组:

  1. ----------------------------------------------
  2. | Year | Month | Income |
  3. ----------------------------------------------
  4. ----------------------------------------------
  5. | 2012 | January | 20,000 |
  6. | 2012 | February | 20,000 |
  7. | 2012 | March | 20,000 |
  8. | 2012 | April | 20,000 |
  9. ----------------------------------------------
  10. etc..

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

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

到目前为止,我已经匹配了第二行到最后一行:

  1. AND c.analysiscode <> 213

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

宝贝步骤!

UPDATE

由于Rails指南网站,我现在已经对过滤进行了排序,但我仍然坚持对SQL查询进行分组和求和.到目前为止我有以下内容

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

我正在努力构建以下两行的SQL查询

  1. sum(c.LineBalance) as "Income"

  1. GROUP BY c.MonthYear;)

我的视图代码如下所示:

  1. <% @clinical_income_by_year.each do |clinical| %>
  2. <tr>
  3. <td><%= clinical.TransactionDate.strftime("%Y") %></td>
  4. <td><%= clinical.TransactionDate.strftime("%B") %></td>
  5. <td><%= Clinical.sum(:LineBalance) %></td>
  6. </tr>
  7. <% end %>
  8. </table>
  9. <%= will_paginate @clinical_income_by_year %>

解决方法

Ruby解析器不理解sql,您需要使用字符串:
  1. @clinical_income_by_year = Clinical.find_by_sql(%q{ ... })

我建议使用%q或%Q(如果你需要插值),这样你就不必担心嵌入式引号了.您还应该将其移动到模型中的类方法中,以防止您的控制器担心不属于他们业务的事情,这也可以让您轻松访问connection.quote和朋友,以便您可以正确使用字符串插值:

  1. find_by_sql(%Q{
  2. select ...
  3. from ...
  4. where x = #{connection.quote(some_string)}
  5. })

另外,sql中的分号:

  1. GROUP BY c.MonthYear;})

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

根据您的数据库,标识符(表名,列名,……)应该不区分大小写(除非一些可恶的人在创建它们时引用它们),这样您就可以使用小写列名来使某些内容适合Rails更好.

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

查询的更“Railsy”版本看起来像这样:

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

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

  1. @c.each do |c|
  2. puts c.year
  3. puts c.month
  4. puts c.income
  5. end

访问结果.你可以通过将日期修改推送到Ruby来简化一点:

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

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

猜你在找的MsSQL相关文章