Trying to search by first_name and last_name

Question

I am trying to search a user by both first and last name in my rails app and am currently getting mixed results for each method I try. Is there a way to re-write any of these methods to get my desired results?

user_controller.rb

method #1

def self.search(query)
  where("first_name LIKE ? OR last_name LIKE ?", "%#{query}%", "%#{query}%") 
end

This works for either first or last name but not both.

Method #2

def self.search(keywords)
  if keywords
    where(:all, :conditions => ["concat(first_name," ",last_name) like?", "%#{keywords}%"])
  end
end

This doesn't return any result

Method#3

def self.search(search)
  if search
    select('(first_name || " " || last_name) as \'ful_name\', *')
    where ['first_name LIKE :s OR last_name LIKE :s OR ful_name LIKE :s', :s => "%#{search}"]
  else
  scoped
 end
end

This returns the error

SQLite3::SQLException: no such column: ful_name: SELECT "users".* FROM "users" WHERE (first_name LIKE '%Spider Man' OR last_name LIKE '%Spider Man' OR ful_name LIKE '%Spider Man') ORDER BY created_at DESC

app/views/users/index.html.erb:5:in `_app_views_users_index_html_erb__848623016_40254132'

index.html.erb

<% provide(:title, 'Search') %>
<h1>Search</h1>

<ul class="span4 users">
  <%= render @users %>
</ul>

_user.html.erb

<li>
  <%= image_tag user.avatar(:medium) %>
  <h4><%= link_to user.full_name, feed_user_path(user), :class => "follow-color" %></h4>
  <% if current_user.admin? && !current_user?(user) %>
    | <%= link_to "delete", user, method: :delete,
                                  data: { confirm: "You sure?" } %>
  <% end %>
</li>

_header.html.erb

<%= form_tag users_path, method: "get", class: "search-bar" do %>
  <%= text_field_tag :search, params[:search], placeholder: "Search" %>
<% end %>

Answer 1

This one:

:conditions => ["concat(first_name," ",last_name) like?", "%#{keywords}%"]

won't work because you have an (insidious) quote problem. In Ruby, this:

"a" "b"

is the same as:

"ab"

so your :conditions is really this:

:conditions => ["concat(first_name,,last_name) like?", "%#{keywords}%"]

You mean to say:

:conditions => ["concat(first_name, ' ', last_name) like ?", "%#{keywords}%"]

String literals in SQL use single quotes, not double quotes. Also, if you're using a database that claims to support standard SQL, you should use the || operator for string concatenation:

:conditions => ["first_name || ' ' || last_name like ?", "%#{keywords}%"]

The third one won't work because aliases defined in a SELECT clause are not generally available in the WHERE clause, hence the "unknown column" error. You're also throwing away the result of the select call so I think you're missing a . in here too:

select('(first_name || " " || last_name) as \'ful_name\', *')
where ['first_name LIKE :s OR last_name LIKE :s OR ful_name LIKE :s', :s => "%#{search}"]

There's also a potential quoting problem: string literals use single quotes in SQL, double quotes are for identifiers. You want to say just:

where("first_name like :s or last_name like :s or first_name || ' ' || last_name like :s", :s => "%#{search}")

or just:

where("first_name || ' ' || last_name like :s", :s => "%#{search}")

A couple caveats:

1. String concatenation operators are database-specific. Standard SQL uses || but, depending on the configuration, MySQL wants to use the concat function. AFAIK, SQLite supports a lot of MySQL-isms but you need to be aware when you're using them and you should stick to the standard as much as possible.
2. Quoting is, again, database-specific. Standard SQL uses single quotes for string literals and double quotes for identifiers (such as table and column names). MySQL uses back-ticks for identifiers, SQLite (AFAIK) lets you use double quotes or back-ticks for identifiers and single or double quotes for strings. Again, stick to the standard as much as possible to build good habits.

Answer 2

I wanted to do the same, i followed this good tuto : https://www.youtube.com/watch?v=s88Uc0InOAM

Then i had the same problem u have, i wanted to search in both name and surname. Here it's what i did and it's working :

    @indiens = Indien.where("name LIKE ?", "%" + params[:q] + "%" ).or(Indien.where("surname LIKE ?", "%" + params[:q] + "%" ))

It may be possible to chain more, but i didn't test.

I know it has been 5 year, maybe it will help others.