Trying to search by first_name and last_name

I am trying to search for a user by first and last name in my rails application and am currently getting mixed results for every method I try. Is there a way to rewrite any of these methods to get the 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 both the former and the 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 returns no 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 an 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 %>

      

+3


source to share


1 answer


This:

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

      

won't work because you have a (insidious) quote problem. In Ruby, these are:

"a" "b"

      

matches:

"ab"

      

so yours is :conditions

really like this:

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

      

You want to say:

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

      



String literals in SQL use single quotes, not double quotes. Also, if you are using a database that claims to support standard SQL, you must use the operator ||

to concatenate strings:

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

      

The third will not work because the aliases defined in the SELECT clause are usually not available in the WHERE clause, hence the "unknown column" error. You are also discarding the result of the call select

, so I think what is missing 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 is also a potential quoting issue: string literals use single quotes in SQL, double quotes for identifiers. You just want to say:

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

      

or simply:

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

      

A couple of warnings:

  • String concatenation operators are database dependent. Standard SQL uses ||

    , but depending on the configuration, MySQL wants to use the function concat

    . AFAIK, SQLite supports many MySQL isms, but you need to know when you are using them and you should stick to the standard as much as possible.
  • The quote, again, depends on the database. Standard SQL uses single quotes for string literals and double quotes for identifiers (such as table and column names). MySQL uses backticks for identifiers, SQLite (AFAIK) allows double quotes or backticks for identifiers, and single or double quotes for strings. Again, stick to the standard as much as possible to build good habits.
+4


source







All Articles