CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-5
Imran Ahmad
Imran Ahmad

Reputation: 2927

OR clause in Rails 5

How can I get (A || B) && ( C || D) query in Rails 5 ActiveRecord?. I tried Post.where(a).or(Post.where(b)).where(c).or(Post.where(d))

but It produces as: (A || B) && C || D. What would be the correct code to get desired query?

Upvotes: 3

Views: 826

Answers (2)

Paul Fioravanti
Paul Fioravanti

Reputation: 16793

Have you tried pushing each of the or queries down into individual named scopes in the Post model? For me, splitting them out yielded different results. Try something like the code below and see if you get the expected results (I didn't have a Rails app handy so I put everything into a single Ruby file):

rails_where_or.rb

require 'active_record'
require 'rspec'

ActiveRecord::Base.establish_connection(
  adapter: 'sqlite3', database: ':memory:'
)

ActiveRecord::Schema.define do
  create_table :posts do |t|
    t.boolean :a, default: false
    t.boolean :b, default: false
    t.boolean :c, default: false
    t.boolean :d, default: false
  end
end

class Post < ActiveRecord::Base
  def self.a_or_b_and_c_or_d
    a_or_b.c_or_d
  end

  def self.a_or_b_and_c_or_d_all_chained
    where(a: true).or(where(b: true)).
      where(c: true).or(where(d: true))
  end

  def self.a_or_b
    where(a: true).or(where(b: true))
  end

  def self.c_or_d
    where(c: true).or(where(d: true))
  end
end

RSpec.describe Post do
  let!(:only_a) do
    described_class.create!(a: true)
  end
  let!(:only_b) do
    described_class.create!(b: true)
  end
  let!(:only_c) do
    described_class.create!(c: true)
  end
  let!(:only_d) do
    described_class.create!(d: true)
  end
  let!(:a_and_b) do
    described_class.create!(a: true, b: true)
  end
  let!(:a_and_c) do
    described_class.create!(a: true, c: true)
  end
  let!(:a_and_d) do
    described_class.create!(a: true, d: true)
  end
  let!(:b_and_c) do
    described_class.create!(b: true, c: true)
  end
  let!(:b_and_d) do
    described_class.create!(b: true, d: true)
  end
  let!(:c_and_d) do
    described_class.create!(c: true, d: true)
  end

  describe '.a_or_b_and_c_or_d' do
    let(:a_or_b_and_c_or_d) do
      described_class.a_or_b_and_c_or_d
    end

    it 'returns only records that match (A || B) && ( C || D )' do
      expect(a_or_b_and_c_or_d).not_to include(only_a)
      expect(a_or_b_and_c_or_d).not_to include(only_b)
      expect(a_or_b_and_c_or_d).not_to include(only_c)
      expect(a_or_b_and_c_or_d).not_to include(only_d)
      expect(a_or_b_and_c_or_d).not_to include(a_and_b)
      expect(a_or_b_and_c_or_d).not_to include(c_and_d)
      expect(a_or_b_and_c_or_d).to include(a_and_c)
      expect(a_or_b_and_c_or_d).to include(a_and_d)
      expect(a_or_b_and_c_or_d).to include(b_and_c)
      expect(a_or_b_and_c_or_d).to include(b_and_d)
    end
  end

  describe '.a_or_b_and_c_or_d_all_chained' do
    let(:a_or_b_and_c_or_d_all_chained) do
      described_class.a_or_b_and_c_or_d_all_chained
    end

    it 'returns records that match (A || B) && C || D ' do
      expect(a_or_b_and_c_or_d_all_chained).not_to include(only_a)
      expect(a_or_b_and_c_or_d_all_chained).not_to include(only_b)
      expect(a_or_b_and_c_or_d_all_chained).not_to include(only_c)
      expect(a_or_b_and_c_or_d_all_chained).not_to include(a_and_b)
      expect(a_or_b_and_c_or_d_all_chained).to include(only_d)
      expect(a_or_b_and_c_or_d_all_chained).to include(c_and_d)
      expect(a_or_b_and_c_or_d_all_chained).to include(a_and_c)
      expect(a_or_b_and_c_or_d_all_chained).to include(a_and_d)
      expect(a_or_b_and_c_or_d_all_chained).to include(b_and_c)
      expect(a_or_b_and_c_or_d_all_chained).to include(b_and_d)
    end
  end
end

Upvotes: 0

Wes Foster
Wes Foster

Reputation: 8900

The Rails or() method is not as complex as this. There is a long discussion about the feature when it was first proposed here, on Github

This method is meant to provide a convenient way of Railsifying queries, but sometimes, things just cannot be that simple. The or() method essentially is stuck with reading left-to-right.

Your best bet while sticking to a "Railsy" convention would be to follow one of the following approaches:

Post.where("a OR b").where("c OR d")             # Example 1 - SQL with additional where()
Post.where(a: [true, !b]).where(c: [true, !d])   # Example 2 - Array comparison
Post.where("(a OR b) AND (c OR d)")              # Example 3 - SQL only

Upvotes: 1

Related Questions