Ruby程序中在另一方法内调用函数返回SQL字符串的最佳方式是什么？

你当前使用的#{companies_data}字符串插值方案可以实现基础的代码复用需求，但并非最优方案，存在明显的适用限制，可结合你的技术栈选择更适配的实现方式。

现有方案的优劣势 #

优势 #

• 实现成本极低，无需依赖额外组件，直接通过Ruby原生字符串插值即可完成复用
• 重复的SQL字段逻辑只需维护一份，修改时无需逐个调整所有查询语句

劣势 #

• 强绑定表别名，只要主查询中companies/company_invoices/case_files的别名和硬编码内容不一致，SQL就会直接报错，复用灵活性非常差
• 拼接逻辑完全裸露，后续如果需要加入动态参数，很容易引入SQL注入风险
• 调试难度高，必须打印插值后的完整SQL才能排查字段拼写、语法等问题
• 如果你使用Rails ActiveRecord等ORM框架，完全没有利用框架提供的更安全、更易维护的查询能力

优化方案 #

场景1：使用Rails ActiveRecord ORM #

推荐将公共查询逻辑封装为Scope，完全贴合ORM的使用习惯：

# 可放在concern中统一管理
module CommonQueryFields
  extend ActiveSupport::Concern

  included do
    scope :with_company_invoice_common_fields, -> {
      select(
        "companies.data->>'creditor_number' as creditor_number",
        "companies.data -> 'pay_to_bank_account' ->> 'iban' AS iban",
        "company_invoices.from_date",
        "company_invoices.to_date",
        "concat(company_invoices.r_number, company_invoices.s_number) as rechnungsnummer",
        "companies.send_invoice_automatically",
        "companies.claim_netting",
        "ROUND(company_invoices.total_cents)/100 AS netting_betrag",
        # 其余公共字段依次添加
        "companies.creditor_payout_by_case_file_invoice"
      ).joins(:case_files, :companies, :company_invoices)
    }
  end
end

# 查询时直接链式调用
def query_for_non_vat_payer
  CaseFileInvoice
    .select("distinct NAME")
    .with_company_invoice_common_fields
    .where("date_part('year', company_invoices.to_date) = ?", 2021)
    .where("date_part('month', company_invoices.to_date) = ?", 7)
    .where(companies: { vat_perspective: 'not_vat_payer', invoicing_cycle: 'monthly' })
    .to_sql
end

该方案的好处：

• 自动支持参数绑定，从根源避免SQL注入风险
• 公共关联逻辑也可以一起封装，无需每次重复写JOIN语句
• 框架会提供更明确的错误提示，排查问题效率更高
• 支持链式组合不同查询条件，扩展灵活性更强

场景2：裸写SQL不使用ORM #

可以给抽取的公共字段方法增加可配置的别名参数，解决硬编码别名的灵活性问题：

def companies_data(company_alias: 'companies', invoice_alias: 'company_invoices', case_file_alias: 'case_files')
  <<~SQL
    #{company_alias}.data->>'creditor_number' as creditor_number,
    #{company_alias}.data -> 'pay_to_bank_account' ->> 'iban' AS iban,
    #{invoice_alias}.from_date,
    #{invoice_alias}.to_date,
    concat(#{invoice_alias}.r_number, #{invoice_alias}.s_number) as rechnungsnummer,
    #{company_alias}.send_invoice_automatically,
    #{company_alias}.claim_netting,
    ROUND(#{invoice_alias}.total_cents)/100 AS netting_betrag,
    ROUND(#{invoice_alias}.billable_dca_fees_recovered_to_creditor_cents + #{invoice_alias}.billable_dca_fees_recovered_to_creditor_vat_cents + #{invoice_alias}.billable_dca_flat_fees_cents + #{invoice_alias}.billable_dca_flat_fees_vat_cents + #{invoice_alias}.billable_dca_success_fees_cents + #{invoice_alias}.billable_dca_success_fees_vat_cents + #{invoice_alias}.billable_tax_free_dca_costs_cents + #{invoice_alias}.billable_taxable_dca_costs_cents + #{invoice_alias}.billable_taxable_dca_costs_vat_cents)/100 AS rechnungsbetrag,
    round(#{invoice_alias}.billable_dca_fees_recovered_to_creditor_cents + #{invoice_alias}.billable_dca_fees_recovered_to_creditor_vat_cents + #{invoice_alias}.billable_dca_flat_fees_cents + #{invoice_alias}.billable_dca_flat_fees_vat_cents + #{invoice_alias}.billable_dca_success_fees_cents + #{invoice_alias}.billable_dca_success_fees_vat_cents + #{invoice_alias}.billable_tax_free_dca_costs_cents + #{invoice_alias}.billable_taxable_dca_costs_cents + #{invoice_alias}.billable_taxable_dca_costs_vat_cents - #{invoice_alias}.total_cents)/100 AS abrechnungsbetrag,
    #{case_file_alias}.currency,
    #{company_alias}.vat_perspective,
    #{company_alias}.creditor_payout_by_case_file_invoice
  SQL
end

# 别名不匹配时直接传参调整即可
# 示例：#{companies_data(company_alias: 'c', invoice_alias: 'ci')}

内容的提问来源于stack exchange，提问作者cbln