ROR Search virtual fields

Question

I am just adding search to my project to be able to find people by name. but on my db i have first_name and last_name but if someone searches for a full name like Joe Doe no result matches

#model 
class Person < ActiveRecord::Base
  def full_name
    (self.first_name + ' ' + self.last_name).titleize
  end
end

#controller 
class PeoplesController < ApplicationController
  expose(:peoples){
    if params[:search]
      People.where(
        "first_name ILIKE ?
         OR last_name ILIKE ?
         ", params[:search], params[:search]
      )
    else
      People.all
    end
  }
end

Again if someone searches a first_name it comes back with results, last_name it comes back with results but not for a full name

and if i try to add full_name to the query i get column "full_name" does not exist

Thanks for the help

the problem is not the wild card search is that i also want to search for the combine values of firs_name + last_name — , Dec 06 '14 at 14:10

score 0 · Answer 1 · answered Dec 06 '14 at 14:12

Again if someone searches a first_name it comes back with results, last_name it comes back with results but not for a full name

That's expected because none of the fields contain the whole name, just the parts, hence they can never match. A simple way out of this is to just split the seatch term by space and check whether you got two or more items.

# app/queries/people_search_query.rb
class PeopleSearchQuery
  attr_reader :relation

  def initialize(relation = Person.all)
    @relation = relation
  end

  def search(params)
    if params[:search]
      where_str = "first_name ILIKE ? OR last_name ILIKE ?"
      split     = params[:search].split(" ", 2)

      if split.size > 1
        relation.where(where_str, *split)
      else
        relation.where(where_str, *(split * 2))
      end
    else
      relation
    end
  end
end

class PeoplesController < ApplicationController
  expose(:peoples) { PeopleSearchQuery.new.search(params) }
end

score 0 · Accepted Answer · answered Dec 06 '14 at 14:47

A virtual field is not on database-level. You can't do database-based search without explaining what that virtual field is to the database.

The definition of your field is essentially a list of columns it consists of. Since you are using PostgreSQL, you could leverage its full-text searching capabilities by using pg_search. It's well able to search by multiple columns at once.

In fact, in the docs there is an example of how to do it that almost exactly matches your case. I literally just copy-pasted it here. Go figure.

# Model
class Person < ActiveRecord::Base
  include PgSearch
  pg_search_scope :search_by_full_name, :against => [:first_name, :last_name]
end

# Example code for the Rails console
person_1 = Person.create!(:first_name => "Grant", :last_name => "Hill")
person_2 = Person.create!(:first_name => "Hugh", :last_name => "Grant")

Person.search_by_full_name("Grant") # => [person_1, person_2]
Person.search_by_full_name("Grant Hill") # => [person_1]

Is that sort of thing worth an extra dependency, is up to you. If you find yourself in situation of constructing many complicated searches, this might help.

Love your solution I have to say thanks this really help me out a lot . — , Dec 06 '14 at 14:59

ROR Search virtual fields

2 Answers2