CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
peter
peter

Reputation: 42207

Ruby Activerecord: find same value in multiple fields

I use the following query in SQL to find the same value in multiple fields of my model but would like to do it in a more correct Activerecord way.

MyModel.where("'some_value' in (a_field, another_field)").first.try(:id)

The following does not work since it's an AND and I need an OR

MyModel.where(a_field: 'some_value', another_field: 'some_value').first.try(:id)

Any suggestions ? Out of curiosity: if I use the first (which works) and use a puts or p to view the result I see the results two times ? I wonder why..

EDIT in this example I use only two fields but in reality there could be more so an or wouldn't be doable and not dry

Upvotes: 1

Views: 2041

Answers (4)

peter
peter

Reputation: 42207

I forgot about this question, here is how I do this now

def search_in_all_fields model, text
  model.where(
    model.column_names
      .map {|field| "#{field} like '%#{text}%'" }
      .join(" or ")
  )
end

Or better as a scope in the model itself

class Model < ActiveRecord::Base
  scope :search_in_all_fields, ->(text){
    where(
      column_names
        .map {|field| "#{field} like '%#{text}%'" }
        .join(" or ")
    )
  }
end

You would just need to call it like this

Model.search_in_all_fields "test"

Before you start.., to be sure there is no sql injection here still better and shorter

class Model < ActiveRecord::Base
  scope :search_all_fields, ->(text){
    where("#{column_names.join(' || ')} like ?", "%#{text}%")
  }
end

Upvotes: 1

rahul mishra
rahul mishra

Reputation: 1470

Myltiple ways to do so:

Rails: 

Post.where('id = 1').or(Post.where('id = 2'))

refs: https://github.com/rails/rails/commit/9e42cf019f2417473e7dcbfcb885709fa2709f89

Use rais_or Gem: https://github.com/khiav223577/rails_or

EX: user = User.where(account: account).or(email: account).take

Use ARel # Arel is best for complex queries

t = Post.arel_table
results = Post.where(
  t[:author].eq("Someone").
  or(t[:title].matches("%something%"))
)

Upvotes: 2

SteveTurczyn
SteveTurczyn

Reputation: 36880

There is an or method available...

MyModel.where(a_field: 'some_value').or(MyModel.where(another_field: 'some_value')).first.try(:id)

for multiple fields you could do 

test_value = 'some_value'

my_models = MyModel.where(id: nil)

%i(a_field another_field third_field fourth_field fifth_field).each do |field|
  my_models = my_models.or(MyModel.where(field => test_value))
end

Upvotes: 4

Nimish Gupta
Nimish Gupta

Reputation: 3175

Ransack is a gem which is used to build complex queries.

It supports both or and and operators.

For searching on multiple columns with same value you can build ransack query in following way.

MyModel.ransack(field1_or_field2_or_field3_eq: 'some_value')

Ransack provide various options to get your result like (equal_to condition, like_condition etc...).

If you don't want to use any external gem then I think @steve's answers is apt.

Upvotes: 2

Related Questions