CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecord
Webspirit
Webspirit

Reputation: 1053

Or & and in Rails ActiveRecord where clause

I'm using Rails 3.2 and I've got a database table in which I want to find all the rows that match the following criteria:

a = true and b = true and ( 0< c <1 or d=1), a, b, c, d are columns.

Can I have something like:

 Route.where(:a => true,
             :b => true,
             :c => 0..1 OR :d=1
             ).all

Upvotes: 22

Views: 39550

Answers (4)

Daniel Garmoshka
Daniel Garmoshka

Reputation: 6352

There is frequently used method missing in ActiveRecords:

Route.where(:a => true, :b => true)
     .where_one_of(:c => 0..1, :d=1)

you can add it with following patch:

module ActiveRecordSearch

  def where_one_of fields
    relation = nil
    fields.each do |key, value|
      next unless value.present?

      where = self.where(key => value)
      relation = relation ? relation.or(where) : where
    end
    relation
  end

end

ActiveRecord::Relation.class_eval do
  include ActiveRecordSearch
end

ActiveRecord::Base.class_eval do
  extend ActiveRecordSearch
end

Upvotes: 0

user2031423
user2031423

Reputation: 367

In Rails 4 you can also do

Route.where(:a => true,:b => true,:c => [1,2]).all

This will find where c is 1 or 2.

Upvotes: 18

pepe
pepe

Reputation: 555

I think Rob is right about arel not supporting OR yet. From the arel site:

The OR operator is not yet supported. It will work like this:

users.where(users[:name].eq('bob').or(users[:age].lt(25)))

The AND operator will behave similarly.

Upvotes: 4

Rob d&#39;Apice
Rob d&#39;Apice

Reputation: 2416

I may be wrong, but I don't think you could form that query using the Arel-based where function; you'd need to form up the database query string yourself.

Assuming you're using SQLite or Postgres:

Route.where("a = true and b = true and ((c > 0 and c < 1) or d = 1)").all

I haven't tested this code, but I suspect that might do the job for you. Note this is less 'portable' code; if you change the database you're using the query may break.

Upvotes: 19

Related Questions