在Rails 3应用程序中使用原始SQL查询? [英] Using raw sql queries in Rails 3 application?

问题描述

我正在将旧数据库迁移到我的Rails应用程序(3.2.3)中.原始数据库带有相当多的长SQL查询报表.现在，我想在Rails应用程序中使用sql查询，然后(在时间允许的情况下)将sql查询一一交换为适当的" Rails查询.

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.

我有一个临床模型，控制器具有以下代码:

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 that code I get a few errors to do with the formatting.

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查询做些什么?尽管查询可能有问题(它是在很早以前编写的)，但是当直接在数据库中运行时，它确实可以按预期工作.它返回这样的数组:

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:

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

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

Any help, advice or general pointers would be appreciated!

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

I'm reading through http://guides.rubyonrails.org/active_record_querying.html trying to convert the sql query to a correct Rails query.

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

So far I have matched the second to last line:

AND c.analysiscode <> 213

使用

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

婴儿的脚步！

更新

由于Rails指南站点，我现在已经对过滤进行了排序，但是我被困在sql查询的分组和求和部分中.到目前为止，我有以下内容:

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:

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

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

I'm struggling to build in the following two lines of the sql query:

sum(c.LineBalance) as "Income"

和

GROUP BY c.MonthYear;)

我的查看代码如下:

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

推荐答案

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

The Ruby parser doesn't understand SQL, you need to use a string:

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

我建议为此使用%q或%Q(如果需要插值)，这样您就不必担心嵌入的引号了.您还应该将其移至模型的类方法中，以防止控制器担心与他们无关的事情，这也将使您可以轻松访问

I'd recommend using %q or %Q (if you need interpolation) for this so that you don't have to worry about embedded quotes so much. You should also move that into a class method in the model to keep your controllers from worrying about things that aren't their business, this will also give you easy access to connection.quote and friends so that you can properly use string interpolation:

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

此外，SQL中的分号:

Also, the semicolon in your SQL:

GROUP BY c.MonthYear;})

不是必需的.一些数据库会允许它通过，但是无论如何您都应该摆脱它.

isn't necessary. Some databases will let it through but you should get rid of it anyway.

根据您的数据库，标识符(表名，列名等)应不区分大小写(除非某些可恶的人在创建它们时引用了它们)，因此您可以使用小写的列名来表示一切都更适合Rails.

Depending on your database, the identifiers (table names, column names, ...) should be case insensitive (unless some hateful person quoted them when they were created) so you might be able to use lower case column names to make things fit into Rails better.

还要注意，某些数据库不喜欢GROUP BY，因为SELECT中的列未进行汇总或分组，因此对于每个组要使用哪个c.transactiondate都有歧义.

Also note that some databases won't like that GROUP BY as you have columns in your SELECT that aren't aggregated or grouped so there is ambiguity about which c.transactiondate to use for each group.

查询的更多"Railsy"版本看起来像这样:

A more "Railsy" version of your query 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 could do things like this:

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

以访问结果.您还可以通过将日期处理推入Ruby来简化一点:

to access the results. You could also simplify a little bit by pushing the date mangling into Ruby:

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

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

Then pull apart c.transactiondate in Ruby rather than calling c.year and c.month.

这篇关于在Rails 3应用程序中使用原始SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！