关联模型 Rails 3 的 ActiveRecord 查询 [英] ActiveRecord Query of associated model Rails 3

问题描述

我在 rails 中有三个模型，Project(date, has_many :project_savings), Usage(month, amount, has_many :project_savings) 和 MonthlyProjectSaving(amount,belongs_to :usages, :projects).

I have three models in rails, Project(date, has_many :project_savings), Usage(month, amount, has_many :project_savings) and MonthlyProjectSaving(amount, belongs_to :usages, :projects).

它的设置使每个项目都有一定数量的节省，这与使用月的数量相对应.我试图找到所有具有相应 project.date >= usage.month 和 usage.amount == 0<的项目节省/code> 以最安全的方式.usage.month 和 project.date 都是日期类型.

It's set up so that each project has a number of savings which correspond to a number of usages months. I'm trying to find all the project savings which have a corresponding project.date >= usage.month, and also a usage.amount == 0 in the most secure way possible. usage.month and project.date are both date types.

下面基本上是我想要得到的，但是我尝试了很多方法并且无法获得正确的语法.

Below is basically what I'm trying to get, but I've tried a number of ways and can't get the syntax right.

在我的项目展示视图中:

In my project show view:

s = @project.monthly_project_savings
s.where(s.usage.month >= @project.date).where(s.amount: 0)

我更喜欢一种不会让它对 SQL 注入开放的解决方案.干杯！

I'd prefer a solution which doesn't leave it open to SQL injections. Cheers!

推荐答案

我想你可能正在寻找类似的东西，但我不确定 monthly_project_savings 是什么，或者什么类型 Usage#月份和项目#日期是.

I think you might be looking for something like this, but I'm not sure what monthly_project_savings is, or what types Usage#month and Project#date are.

s.joins(:usages).where('usages.month >= ?', @project.date).where(amount: 0)

在字符串中使用带有占位符的 .where 非常好，因为参数会自动适当地引用.您应该避免使用不受信任的参数进行直接 SQL 修改或插值.更多信息:http://guides.rubyonrails.org/security.html#sql-injection

Using .where with placeholders in strings is perfectly fine, since the arguments are automatically quoted appropriately. It's direct SQL modification or interpolation with untrusted parameters that you should avoid. More information: http://guides.rubyonrails.org/security.html#sql-injection

简而言之:在视图中进行查询不是很 MVC；最好在控制器中进行，或者在模型中更好.

Short aside: doing queries in a view isn't very MVC; it be better to do it in the controller or, even better, in a model.

这篇关于关联模型 Rails 3 的 ActiveRecord 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！