Rspec assert that two sql queries are the same

Question

I am using rpsec to assert that a certain method generates the expected query:

describe '.customers' do

    let(:segment) { create(:segment) }
    
    let!(:rule1) do
      Rule.create!(segment: segment, filter_key: 'generic',
                   attr_name: 'first_name', operator: '=', values: ['john'])
    end
    
    let!(:rule2) do
      Rule.create!(segment: segment, filter_key: 'generic',
                   attr_name: 'orders_count', operator: '>=', values: ['3'])
    end
    
    let(:actual_query) { segment.customers.to_sql }
    let(:expected_query) do
      Customer
        .where('first_name = john')
        .where('orders_count >= 3')
        .to_sql
    end
    
    it 'applies all the rules scopes to customer' do
      expect(actual_query).to eq(expected_query)
    end

end

It usually passes, the thing is that sometimes (for some reason) the where clauses in one of the sql queries get reordered so, since the to_sql is actually the string of the query to execute, sometimes it fails saying:

Failure/Error: expect(actual_query).to eq(expected_query)

 expected: "SELECT \"customers\".* FROM \"customers\" WHERE (orders_count >= '3') AND (first_name = 'john')"

    got: "SELECT \"customers\".* FROM \"customers\" WHERE (first_name = 'john') AND (orders_count >= '3')"
 (compared using ==)

when they are actually the same in terms of the execution. Any ideas on how else to assert the two queries are the same without having to add test customers to compare the queries after they executed?

mrzasa · Accepted Answer · 2019-01-16 13:33:44Z

1

It looks that it is related to ordering of your rules in segment.customers. As they are created one just after another, it's possible that their timestamps in created_at are the same.

I would order them explicitly and add data that supports it in the spec, e.g.:

let!(:rule1) do
  Rule.create!(segment: segment, filter_key: 'generic',
               attr_name: 'first_name', operator: '=', values: ['john'],
               created_at: 2.days.ago)
end

let!(:rule2) do
  Rule.create!(segment: segment, filter_key: 'generic',
               attr_name: 'orders_count', operator: '>=', values: ['3'],
               created_at: 1.day.ago)
end

answered Jan 16, 2019 at 13:33

mrzasa

23.4k11 gold badges60 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Leticia Esperon Over a year ago

What if when I create the query I just concat the rules using rules.order(:id).each do .... would that be enough? (asking because it's hard to test since error appears rarely)

mrzasa Over a year ago

That should be enough, if your database assign ids monotonically, i.e. next id is bigger than the previous one.

Leticia Esperon Over a year ago

Awesome. I will make sure to run it a couple times before marking as answer thank you!

Collectives™ on Stack Overflow

Rspec assert that two sql queries are the same

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related