CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-5
Cannon Moyer
Cannon Moyer

Reputation: 3164

Ruby on Rails query where equal to two concatenated columns

My customer model has a first_name and last_name field. I can query the model with the following code:

Customer.where(first_name: "John").where(last_name "Doe")

However, I want to query the model like this:

Customer.where(full_name: "John Doe")

But I do not have a full_name column. How can I accomplish this without creating a full_name field?

Upvotes: 4

Views: 1493

Answers (3)

Chloe
Chloe

Reputation: 26264

As an alternative to Max, split the name and query on both fields as in your question.

scope :where_full_name, ->(full_name) do 
  (first_name, last_name) = full_name.split(/\s+/)
  where first_name: first_name, last_name: last_name
end

Then use

Customer.where_full_name 'Betty Davis'

This won't work for names like Joan van der Graff.

Upvotes: 1

max
max

Reputation: 102164

You can use the CONCAT operator in SQL:

Customer.where("CONCAT(first_name, ' ', last_name) = ?", full_name)

Which actually might make sense if you are creating something like a search function with like/ilike:

Customer.where("CONCAT(first_name, ' ', last_name) LIKE ?", "%#{full_name}%")

Upvotes: 11

vzamanillo
vzamanillo

Reputation: 10444

You can not, if you do not have a full name attribute you can not query by a full name and any try to do it using ActiveRecord in a single query will add an extra complexity to your app (or model), but to solve your question with simplicity you can add a scope to your Customer model, or a class method passing the first and last name as argument.

Using a scope

scope :by_full_name, (lambda { |first_name, last_name|
  where(first_name: first_name, last_name: last_name)
})

using a static (class) method:

def self.by_full_name(first_name, last_name)
  where(first_name: first_name, last_name: last_name)
end

Personally, due to simplicity and clearly code I prefer to use scopes instead of class methods if no extra logic is required.

Upvotes: 1

Related Questions