Ruby on Rails 3 howto make 'OR' condition
Ruby on-RailsRubyRuby on-Rails-3Ruby on-Rails Problem Overview
I need an SQL statement that check if one condition is satisfied:
SELECT * FROM my_table WHERE my_table.x=1 OR my_table.y=1
I want to do this the 'Rails 3' way. I was looking for something like:
Account.where(:id => 1).or.where(:id => 2)
I know that I can always fallback to sql or a conditions string. However, in my experience this often leads to chaos when combining scopes. What is the best way to do this?
Another related question, is how can describe relationship that depends on an OR condition. The only way I found:
has_many :my_thing, :class_name => "MyTable", :finder_sql => 'SELECT my_tables.* ' + 'FROM my_tables ' +
'WHERE my_tables.payer_id = #{id} OR my_tables.payee_id = #{id}'
However, these again breaks when used in combinations. IS there a better way to specify this?
Ruby on-Rails Solutions
Solution 1 - Ruby on-Rails
Account.where(id: [1,2])
no explanation needed.
Solution 2 - Ruby on-Rails
This will works in Rails 5, see rails master :
Post.where('id = 1').or(Post.where('id = 2'))
# => SELECT * FROM posts WHERE (id = 1) OR (id = 2)
For Rails 3.0.4+:
accounts = Account.arel_table
Account.where(accounts[:id].eq(1).or(accounts[:id].eq(2)))
Solution 3 - Ruby on-Rails
Those arel queries are unreadable to me.
What's wrong with a SQL string? In fact, the Rails guides exposes this way as the first way to make conditions in queries: http://guides.rubyonrails.org/active_record_querying.html#array-conditions
So, I bet for this way to do it as the "Rails way":
Account.where("id = 1 OR id = 2")
In my humble opinion, it's shorter and clearer.
Solution 4 - Ruby on-Rails
Sadly, the .or isn't implemented yet (but when it is, it'll be AWESOME).
So you'll have to do something like:
class Project < ActiveRecord::Base
scope :sufficient_data, :conditions=>['ratio_story_completion != 0 OR ratio_differential != 0']
scope :profitable, :conditions=>['profit > 0']
That way you can still be awesome and do:
Project.sufficient_data.profitable
Solution 5 - Ruby on-Rails
I'd go with the IN
clause, e.g:
Account.where(["id in (?)", [1, 2]])
Solution 6 - Ruby on-Rails
I've used the Squeel gem (https://github.com/ernie/squeel/) to do OR queries and it works beautifully.
It lets you write your query as Account.where{(id == 1) | (id == 2)}
Solution 7 - Ruby on-Rails
You can define an Array as value in the :conditions
Hash.
So you could do for example:
Account.all(:conditions => { :id => [1, 2] })
Tested with Rails 3.1.0
Solution 8 - Ruby on-Rails
Alternate syntax using Hash
Account.where("id = :val1 OR id = :val2", val1: 1, val2: 2).
This is particularly useful, when the value is compared with multiple columns. eg:
User.where("first_name = :name OR last_name = :name", name: 'tom')
Solution 9 - Ruby on-Rails
With rails_or, you could do it like:
Account.where(id: 1).or(id: 2)
(It works in Rails 4 and 5, too.)