Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rails virtual attribute search or sql combined column search

I have a user model with attributes 'first' and 'last' So for example User.first.first #=> "Charlie" User.first.last #=> "Brown"

This User model also has a virtual attribute 'full_name'

#user.rb
def full_name
  [first,last].join(' ')
end

def full_name=(name) #don't know what to do with people w/ middle names
  split = name.split(' ')
  self.first = split[0]
  self.last = split[1]
end

So for example:

User.first.full_name = "Charlie Brown" #=> "Charlie Brown"
User.first.full_name = "Homer Simpson" #=> "Home Simpson"
User.first.save
User.first.first #=> "Homer"
User.first.last #=> "Simpson"

It'd be so nice if I could search by that virtual attribute so for example for dynamic find:

User.find_by_full_name('Home Simpson') # this doesn't work

Example for conditions in find:

User.all(:conditions => ['full_name LIKE ?', query]) #this doesn't work

I am hoping to find at least some ways in SQL language that can do this; if there's a dynamic virtual attribute find, too, that's extra vanilla source on the strudel. (anyone having this this winter?)

I was also concerned about a name being searched , e.g., "Holmes" may only be searched in the 'first' column but not the 'last' to retrieve, for example, User.first.full_name #=> "Sherlock Holmes".

I did try to do a more comprehensive search:

user.rb

def self.find_by_full_name(name) #returns an array of User model
  return all if name.blank?
    
  split = name.split(' ', 2)
  output = []
  if split.length > 1
    with_scope( :find => { :conditions => ['first LIKE ?', "%#{split[0]}%"] }) do
      output << all(:conditions => ['last LIKE ?', "%#{split[1]}%"])
      output.flatten!
    end
  elsif split.length == 1
    output << all(:conditions => ['first LIKE ?', "%#{split[0]}%"])
    output << all(:conditions => ['last LIKE ?', "%#{split[0]}%"])
    output.flatten!
  end
end

For example

User.find_by_full_name("John").map(&:full_name) #=> ["John Resig", "John Doe"]
User.find_by_full_name("Doe").map(&:full_name) #=> ["John Doe", "Philips Doeringer"]
User.find_by_full_name("John Doe").map(&:full_name) #=> ["John Doe"]

But I just thought that the find_by_full_name method here is a bit unwieldy.

I mean, if I had a column full_name that gets set each time by a after save filter with the concat of first and last. So finding a person's name, especially with fuzzy memory of this person, is helpful. So if I remembered 'Doe' in that person's either first or last name, I can always do a simple User.find_by_full_name('Doe') to return as many as possible to further pin it down.

And since it is a column, I can search it in a find(:conditions[...]) clause if I have to do something like Project.find(:all,:include => :users, :conditions=>['users.full_name LIKE ?', query]) where

#project.rb
has_many :assignments
has_many :users, :through=>:assignments

#user.rb
has_many :assignments
has_many :projects, :through => :assignments

#assignment.rb
belongs_to :user
belongs_to :project

Happy Holidays N

like image 444
Nik So Avatar asked Nov 27 '09 01:11

Nik So


1 Answers

You can used a named_scope in your user.rb:

named_scope :find_by_full_name, lambda {|full_name| 
  {:conditions => {:first => full_name.split(' ').first, 
     :last => full_name.split(' ').last}}
}

Then you can do User.find_by_full_name('John Carver')

new stuff in response to changes in requirement

named_scope :find_by_full_name, lambda {|full_name| 
  {:conditions => ["first LIKE '%?%' or last LIKE '%?%'", 
    full_name.split(' ').first, full_name.split(' ').last]}}
like image 159
Jim Avatar answered Oct 19 '22 09:10

Jim