CODEWITHSUNDEEP
ruby-on-railsactiverecord
Lewy
Lewy

Reputation: 719

Get multiple records with one query

User table:

name lastname

Bob Presley
Jamie Cox
Lucy Bush
Roman Cox

Find users

q = Query.new("Bob Presley, Cox, Lucy")
q.find_users => {0=>{:name=>"Bob", :lastname=>"Presley"}, 1=>{:lastname=>"Cox"}, 2=>{:name=>"Lucy"}}

Question:

I've got hash with few names and lastnames. I need to build Activerecord query to fetch all users from that hash.

If i have name and lastname I should find user with exactly the same name and lastname.

If I have only lastname or name I should find all users with this name or lastname. So when i search for :lastname => Cox it should return two users [Roman Cox,Jamie Cox]

I can do

object = []
hash = q.find_users
hash.each do |data|
 #Pseudocode
 # object << User.where(:name => if data[:lastname] exist, :lastname => if data[:name] exist)
end

But I think it is higly inefficient. How should I do this ?

Environment

rails: 3.0.3
ruby: 1.9.2-head
gem: meta_search https://github.com/ernie/meta_search

Upvotes: 0

Views: 1231

Answers (1)

Zabba
Zabba

Reputation: 65467

I'm sure this can be refactored nicely (hint!), but this code below will construct a SQL which can be used in a sub-select.

Code below does not sanitize the input values. Note that you should sanitize the values in the h hash!

h =  {0=>{:name=>"Bob", :lastname=>"Presley"}, 1=>{:lastname=>"Cox"}, 2=>{:name=>"Lucy"}}
conditions = ""
h.each_pair do |k,v|
  if not conditions.empty?
    conditions += " or "
  end
  conditions += "("
  a_condition = ""
  v.each_pair do |a,b|
    if not a_condition.empty?
      a_condition += " and "
    end
    a_condition += "#{a.to_s} = '#{b}'"
  end
  conditions += a_condition
  conditions += ")"
end
conditions = "("+conditions+")"    
p conditions
# => "((name = 'Bob' and lastname = 'Presley') or (lastname = 'Cox') or (name = 'Lucy'))"

# use the generated SQL conditions to find the users
@users = User.find(:all, :conditions => "(#{conditions})")

Upvotes: 1

Related Questions