CODEWITHSUNDEEP
ruby-on-rails
Kevin Sylvestre
Kevin Sylvestre

Reputation: 38012

Active Record Find By Column 'OR' Rails

Is it possible to perform a find_by query using an 'or' statement? For example:

@product ||= Product.find_by_upc(params[:code]) if params[:code]
@product ||= Product.find_by_cspc(params[:code]) if params[:code]

As something like (does not work):

@product ||= Product.find_by_upc_or_cspc(params[:code]) if params[:code]

Thanks!

Upvotes: 9

Views: 17732

Answers (7)

alex
alex

Reputation: 1089

As of Rails 6, ActiveRecord has an or operator.

It doesn't work exactly how you'd expect, as you need to create a brand new query inside the or. But it's nice to avoid needing raw SQL.

Person.where(first_name: "John").or(Person.where(last_name: "Smith"))

This generates the following SQL under the hood.

SELECT "people".* FROM "people" WHERE ("people"."first_name" = $1 OR "people"."last_name" = $2) LIMIT $3
[["first_name", "John"], ["last_name", "Smith"], ["LIMIT", 11]]

Upvotes: 6

jayqui
jayqui

Reputation: 1899

As of Rails 5, one way to do this is as follows:

For the first matching record:

Product.find_by("upc = ? OR cspc = ?", params[:code], params[:code])

For all matching records:

Product.where("upc = ? OR cspc = ?", params[:code], params[:code])

Upvotes: 23

nicholasklick
nicholasklick

Reputation: 1212

Not using Activerecord methods

This might work:

code = params[:code]
@product = Product.find(:all, :conditions => ["upc = ? or cspc = ?", code, code])

Upvotes: 9

Dan McNevin
Dan McNevin

Reputation: 22336

If you're using Arel on Rails 3:

t = Product.arel_table

@products = Product.where(
  t[:upc].eq(params[:code])       \
  .or(t[:cspc].eq(params[:code]))
)

@product = @products.first if @products.size == 1

Upvotes: 2

Omar Qureshi
Omar Qureshi

Reputation: 9093

Cleaning up nicholas' code:

@products = Product.all(:conditions => ["upc = :code or cspc = :code", {:code => params[:code]}])

Upvotes: 6

user94154
user94154

Reputation: 16564

I've had a similar problem like this before.

One way to approach it is to try and figure out what type of data you're getting in the first place. Write some code that will tell you the difference.

Maybe see if there is a regex way to distinguish between UPC and CSPC.

Upvotes: 1

bjg
bjg

Reputation: 7477

As far as I know Rails doesn't support automatic OR finders, out of the box. But the searchlogic gem appears to support this feature. See here

Upvotes: 1

Related Questions