ruby-on-rails - 使用 Rails 4 对象将 PSQL 查询转换为工作

Question

我有以下查询:

select * from email_contacts
where extract(month from created_at) = 4
and call_type = 'Membership NPS'
and email NOT IN
(
    select invite_email from nps_responses
    where extract(month from fs_created_at) = 4
    and invite_email is not null
)

然后我有一个 Rails 4 应用程序，它有两个对应的模型，EmailContact 和 NpsResponse。

如何将此查询转换为在 Rails 中的 Controller 方法中运行？我需要选择符合上述条件的所有 email_contacts。

Answer 1

这可能不是完美的“Rails 方式”解决方案，但您可以使用 find_by_sql 来实现.使用 find_by_sql 仍会检索模型对象的实例化对象:

EmailContact.find_by_sql(your_sql_statement)

使用绑定(bind)变量

为了防止SQL注入(inject)，你应该使用bind variables使用 find_by_sql 方法(绑定(bind)变量是由 ? 字符表示的占位符参数。find_by_sql 方法将在执行语句之前绑定(bind)参数值。 ) 从安全角度来看，强烈建议这样做。

这是在 find_by_sql 方法中使用带有绑定(bind)变量的 SQL 语句的示例:

EmailContact.find_by_sql("select * from email_contacts
          where extract(month from created_at) = ?
          and call_type = ?
          and email NOT IN
          (
              select invite_email from nps_responses
              where extract(month from fs_created_at) = ?
              and invite_email is not null
          )", 4, 'Membership NPS', 4)

注意事项:

• find_by_sql 方法的第一个参数是作为字符串值的 SQL 语句。在这个特定的 SQL 字符串中，有三个 ? 字符作为占位符变量。
• find_by_sql 中接下来的三个参数是要绑定(bind)到 SQL 语句中的 ? 字符的值。
• 参数的顺序很重要 - 第一个绑定(bind)变量参数将绑定(bind)到第一个 ? 字符等。
• 您可以拥有可变数量的绑定(bind)变量参数，只要 ? 的数量与绑定(bind)变量参数的数量相匹配。

为了更好地组织代码，您可以使用某种辅助方法来构造带有绑定(bind)变量的 SQL 语句。有点像这样:

def email_contacts_SQL
   sql = "select * from email_contacts
          where extract(month from created_at) = ?
          and call_type = ?
          and email NOT IN
          (
              select invite_email from nps_responses
              where extract(month from fs_created_at) = ?
              and invite_email is not null
          )"
end

然后将 find_by_sql 与您的辅助方法一起使用:

EmailContact.find_by_sql(email_contacts_SQL, 4, 'Membership NPS', 4)

official Rails API doc有更多关于如何使用 find_by_sql 的示例。

警告:如果您使用find_by_sql，您将失去ActiveRecord 提供的与数据库无关的转换。 find_by_sql 方法按原样执行 SQL 语句。