使用 SQL 將方法邏輯移動到 Rails 項目中的模型 (Moving method logic with SQL to model in rails project)

問題描述

使用 SQL 將方法邏輯移動到 Rails 項目中的模型 (Moving method logic with SQL to model in rails project)

將我的控制器 (Invoices) 中的方法移動到相應的模型中，但我遺漏了一些東西。我試過關注 this 和 this 甚至 this 還有一些小問題需要我多加註意。

我的工作控制器方法是這個。

  def array_of_disbursable_invoices
    sql = "SELECT MIN(departure_date), ch_invoice.invoice_id
    FROM ch_invoice
    INNER JOIN ch_trip
    ON ch_invoice.invoice_id = ch_trip.invoice_id
    WHERE departure_date <= (SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP)FROM DUAL)
    AND service_rendered = 0
    AND paid = 1
    Group By ch_invoice.invoice_id"

    report = ActiveRecord::Base.connection.exec_query(sql)
    render json: report
  end

我正試圖把它變成這個。

  def array_of_disbursable_invoices
    report = report.array_of_disbursable_invoices
    render json: report
  end

這裡有我模型中的邏輯。<

參考解法

方法 1:

You beat me to it (and went, essentially, with my second option). But, I'll post this anyway.

When you do this:

def array_of_disbursable_invoices
  report = report.array_of_disbursable_invoices
  render json: report
end

You're calling array_of_disbursable_invoices on an instance. But, you don't instantiate Report ‑ thus, the undefined method 'array_of_disbursable_invoices' for nil:NilClass error.

So, I think you have two choices:

(1) You could call the method on an instance, something like:

report = Invoice.new.array_of_disbursable_invoices

(2) You could make the method a class method, something like:

class Invoice < ActiveModel::Base
  class << self
    def array_of_disbursable_invoices
      sql = "SELECT MIN(departure_date), ch_invoice.invoice_id
      FROM ch_invoice
      INNER JOIN ch_trip
      ON ch_invoice.invoice_id = ch_trip.invoice_id
      WHERE departure_date <= (SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP)FROM DUAL)
      AND service_rendered = 0
      AND paid = 1
      Group By ch_invoice.invoice_id"

      connection.exec_query(sql)
    end
  end
end

I think I'd recommend (1). Also, personally, I'd use the ActiveRecord query interface instead of the SQL (assuming you have all the associations set up in your models). But, that's a personal preference.

方法 2:

Got it to work with the following code in my controller.

  def array_of_disbursable_invoices
    report = Invoice.array_of_disbursable_invoices
    render json: report
  end

And this in the model.

  def self.array_of_disbursable_invoices
    sql = "SELECT MIN(departure_date), ch_invoice.invoice_id
    FROM ch_invoice
    INNER JOIN ch_trip
    ON ch_invoice.invoice_id = ch_trip.invoice_id
    WHERE departure_date <= (SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP)FROM DUAL)
    AND service_rendered = 0
    AND paid = 1
    Group By ch_invoice.invoice_id"

    ActiveRecord::Base.connection.exec_query(sql)
  end

(by CheeseFry、jvillian、CheeseFry)

參考文件

1. Moving method logic with SQL to model in rails project (CC BY‑SA 2.5/3.0/4.0)