是否有意义到DB-ISH查询转换为Rails的ActiveRecord模型行话?

mysql> desc categories; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(80) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ mysql> desc expenses; +-------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | created_at | datetime | NO | | NULL | | | description | varchar(100) | NO | | NULL | | | amount | decimal(10,2) | NO | | NULL | | | category_id | int(11) | NO | MUL | 1 | | +-------------+---------------+------+-----+---------+----------------+

现在我需要的前N类这样的...

Expense.find_by_sql("SELECT categories.name, sum(amount) as total_amount from expenses join categories on category_id = categories.id group by category_id order by total_amount desc")

但是,这是在唠叨我的Rails的良心......似乎有可能实现通过Expense.find同样的事情,像提供选择:组:加入..

  • 谁能把这个查询到ActiveRecord模型说话?
  • 这样做值得吗?我个人认为SQL更具可读性,并得到我更快地完成工作..也许怎么把我还在学习Rails的。 有没有嵌入的SQL源代码中有什么优势(除了不能够改变DB vendors..sql香精等)?
  • 好像find_by_sql没有像绑定变量提供find 。 有什么解决办法? 例如,如果我想要的记录数限制为用户指定的限制。

--------------解决方案-------------

Expense.find(:all,
:select => "categories.name name, sum(amount) total_amount",
:joins => "categories on category_id = categories.id",
:group => "category_id",
:order => "total_amount desc")

希望帮助!

好像的find_by_sql没有像找到绑定变量规定。

它肯定不会。 (从Rails的文档)

# You can use the same string replacement techniques as you can with ActiveRecord#find
Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date]

嗯,这是最终为我工作的代码..(弗朗索瓦。得到的SQL语句缺少join关键字)

def Expense.get_top_n_categories options={}
#sQuery = "SELECT categories.name, sum(amount) as total_amount
# from expenses
# join categories on category_id = categories.id
# group by category_id
# order by total_amount desc";
#sQuery += " limit #{options[:limit].to_i}" if !options[:limit].nil?
#Expense.find_by_sql(sQuery)
query_options = {:select => "categories.name name, sum(amount) total_amount",
:joins => "inner join categories on category_id = categories.id",
:group => "category_id",
:order => "total_amount desc"}
query_options[:limit] = options[:limit].to_i if !options[:limit].nil?
Expense.find(:all, query_options)
end

的find_by_sql确实有轨绑定变量......我不知道我怎么没理会这个。 最后就是上面使用的用户指定一个潜在的切入点SQL注入还是在to_i方法调用阻止?

感谢所有帮助。 我很感激​​。

分类:Ruby on Rails的 时间:2015-03-15 人气:0
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.492 (s). 10 q(s)