Moving method logic with SQL to model in rails project

Question

Moving a method in my controller (Invoices) into the corresponding model and I'm missing something. I've tried following this and this and even this and there are small issues I'm having that I need extra eyes on.

My working controller method is 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

And I'm trying to turn it into this.

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

With the logic in my model here.

  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"

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

Current error message

undefined method `array_of_disbursable_invoices' for nil:NilClass

score 1 · Accepted Answer · answered Apr 11 '16 at 16:07

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.

I'll probably go with this answer. You explain it better than I do and it'll probably be more helpful for posterity sake. As for the SQL vs ActiveRecord, I think my team lead just wants me to better understand what ActiveRecord is doing for me by forcing me to build SQL first. May refactor that too. Any other reasons for preferring the former over the latter? — CheeseFry, Apr 11 '16 at 16:14
When I first read you post, I was thinking there might be a reason you were using the `report.array_of_disbursable_invoices` approach (like, maybe there was a `Report` object lurking in here somewhere). I looks like that is not the case, so I think a class method is clean. When you go to refactor (if you decide to do that), you may end up wanting to use a scope instead of a class method. There are good posts on the pros and cons. — jvillian, Apr 11 '16 at 18:04

score 0 · Answer 2 · answered Apr 11 '16 at 16:03

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

Moving method logic with SQL to model in rails project

2 Answers2