Ruby Activerecord: find same value in multiple fields

Question

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

_"I need an OR"_ – here it is: [`or`](http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-or) — Stefan, May 15 '18 at 14:54
possible dupe of https://stackoverflow.com/questions/3639656/activerecord-or-query — lacostenycoder, May 15 '18 at 15:01
@lacostenycoder the question is not for an or but for the better activerecord way, if ther would be multiple fields an or is no good — peter, May 15 '18 at 15:40

SteveTurczyn · Accepted Answer · 2018-05-15T19:11:34.937

4

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

edited May 15 '18 at 19:11

answered May 15 '18 at 15:10

SteveTurczyn

36,057
6
41
53

thx Steve,in my example that could be used, but in reality there are multiple fields and it isn't DRY , if no better suggestion I'll accept and will have to live with the SQL solution iaa – peter May 15 '18 at 15:42
I've edited to suggest a way you could do it for multiple fields. I'm not in a position to test this chaining but it should work? – SteveTurczyn May 15 '18 at 19:10
1

Sometimes SQL is the best way to go, though. :) – SteveTurczyn May 15 '18 at 19:10

score 2 · Answer 2 · edited May 15 '18 at 16:55

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.

score 2 · Answer 3 · answered May 15 '18 at 19:31

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%"))
)

score 1 · Answer 4 · answered Nov 09 '18 at 19:51

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

Ruby Activerecord: find same value in multiple fields

4 Answers4