Rails 虚拟属性搜索或 sql 组合列搜索 [英] Rails virtual attribute search or sql combined column search
问题描述
我有一个具有first"和last"属性的用户模型所以例如User.first.first #=>查理"用户.first.last #=>棕色"
I have a user model with attributes 'first' and 'last' So for example User.first.first #=> "Charlie" User.first.last #=> "Brown"
这个用户模型还有一个虚拟属性'full_name'
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
例如:
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
查找条件示例:
User.all(:conditions => ['full_name LIKE ?', query]) #this doesn't work
我希望至少能在 SQL 语言中找到一些可以做到这一点的方法;如果也有动态虚拟属性查找,那就是馅饼上的额外香草来源.(今年冬天有人过吗?)
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?)
我还担心被搜索的名字,例如Holmes".只能在 'first' 列中搜索,而不是在 'last' 中检索,例如 User.first.full_name #=>夏洛克·福尔摩斯"
.
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:
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
例如
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"]
但我只是觉得这里的 find_by_full_name 方法有点笨拙.
But I just thought that the find_by_full_name method here is a bit unwieldy.
我的意思是,如果我有一个 full_name 列,它每次都由一个后保存过滤器设置,并带有 first 和 last 的 concat.所以找到一个人的名字,尤其是对这个人的模糊记忆,是有帮助的.因此,如果我记得那个人的名字或姓氏中的Doe",我总是可以做一个简单的 User.find_by_full_name('Doe') 来返回尽可能多的信息以进一步确定它.
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.
由于它是一列,如果我必须执行类似 Project.find(:all,:include => 之类的操作,我可以在 find(:conditions[...]) 子句中搜索它:users, :conditions=>['users.full_name LIKE ?', query])
在哪里
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
节日快乐否
推荐答案
你可以在你的user.rb中使用named_scope:
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}}
}
然后你可以做 User.find_by_full_name('John Carver')
响应需求变化的新东西
named_scope :find_by_full_name, lambda {|full_name|
{:conditions => ["first LIKE '%?%' or last LIKE '%?%'",
full_name.split(' ').first, full_name.split(' ').last]}}
这篇关于Rails 虚拟属性搜索或 sql 组合列搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!