4

I've made a custom SQL query and I want to create a unit tests to demonstrate that it works. I have tested it in SQLDeveloper and it works as I want it to on my test database but I want to leave a unit test for those who have to maintain this code later. 

def report_of_merchants_who_have_not_pressed_the_service_rendered_button
    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 < SYSDATE
    AND service_rendered = 0
    AND paid = 1
    Group By ch_invoice.invoice_id"

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

My thought was to create four invoices, three of which are not meeting the above criteria and one that does. Where I am getting stuck is how to check that the query only returns back one response. How do I test that one row of the query is coming out?

Clarification I was intending on creating these invoices & trips with FactoryGirl to fill the models. Will that still work?

CheeseFry
  • 1,299
  • 1
  • 21
  • 38
  • In general, unit tests should not depend on outside services. A typical unit test would mock the response from the test database and test that processing the returned data is done properly. The reason is that once you write a unit test that depends on that database, the unit tests will have to be changed every time you change your database implementation. That isn't really a unit test, it's an integration test. TLDR: You can do this, but it is a bad idea. More info here: http://stackoverflow.com/questions/10752/what-is-the-difference-between-integration-and-unit-tests – nhouser9 Apr 06 '16 at 16:53
  • I don't see why unit tests can't talk to a database. Changing the db implementation is not really an issue because it is not likely to happen and if it does then this test would be thrown away with the database. – B Seven Apr 06 '16 at 16:57
  • @BSeven Because when unit tests start talking to external services, they aren't unit tests by definition. They are integration tests, which should be maintained separately. – nhouser9 Apr 06 '16 at 17:01
  • I guess if you think of the DB as an external service, then this would be an integration test. – B Seven Apr 06 '16 at 17:03
  • 1
    Yes, FactoryGirl is a good way to generate the records. – B Seven Apr 06 '16 at 21:06

1 Answers1

1

Wrap this method in a class.

class MerchantReport
  class << self
    def for_not_pressed_the_service_rendered_button
      sql = "SELECT MIN(departure_date), ch_invoice.invoice_id
      ....    
      ActiveRecord::Base.connection.exec_query(sql)
    end

In your test,

report = MerchantReport.for_not_pressed_the_service_rendered_button

FWIW, I don't think queries like reports have anything to do with Invoices or other models, and belong in their own class.

Yes, you can create 4 invoices in your setup. Also, you can create a separate test for each condition that you want to test. For example, you can have one test for date (departure_date < SYSDATE). That may be more readable than having one test for all conditions.

B Seven
  • 44,484
  • 66
  • 240
  • 385