Please help me in the following query with rails ORM’s style:
Following is the schema
create_table “accounts”, :force => true do |t|
t.integer “credit”
t.integer “debit”
t.integer “balance”
t.integer “contact_id”
t.datetime “created_at”
t.datetime “updated_at”
end
create_table “companies”, :force => true do |t|
t.integer “contact_id”
t.string “website”
t.string “company_name”
t.string “country”
t.integer “industry_id”
t.datetime “created_at”
t.datetime “updated_at”
end
create_table “contacts”, :force => true do |t|
t.string “email”
t.string “password”
t.integer “balance”, :default => 0
t.string “activation_code”
t.integer “contact_type”
t.integer “status_id”
t.datetime “created_at”
t.datetime “updated_at”
end
create_table “contacts_users”, :force => true do |t|
t.integer “contact_id”
t.integer “peer_id”
t.integer “status_id”
t.datetime “created_at”
t.datetime “updated_at”
end
create_table “excel_attachments”, :force => true do |t|
t.integer “size”
t.string “content_type”
t.string “filename”
t.binary “data”
t.datetime “created_at”
t.datetime “updated_at”
end
create_table “industries”, :force => true do |t|
t.string “name”
t.datetime “created_at”
t.datetime “updated_at”
end
create_table “levels”, :force => true do |t|
t.string “name”
t.datetime “created_at”
t.datetime “updated_at”
end
create_table “profile_images”, :force => true do |t|
t.integer “profile_id”
t.integer “contact_id”, :null => false
t.integer “parent_id”
t.string “content_type”, :null => false
t.string “filename”, :null => false
t.string “thumbnail”
t.integer “size”, :null => false
t.integer “width”
t.integer “height”
t.datetime “created_at”
t.datetime “updated_at”
end
create_table “profiles”, :force => true do |t|
t.string “first_name”
t.string “last_name”
t.integer “phone”
t.string “phone_numbers”
t.integer “city_code”
t.integer “phone_country_code”
t.integer “cell_phone”
t.integer “cell_phone_prefix”
t.integer “cell_phone_country_code”
t.string “country”
t.integer “contact_id”
t.datetime “created_at”
t.datetime “updated_at”
end
create_table “searches”, :force => true do |t|
t.integer “contact_id”
t.integer “peer_profile_id”
t.integer “status_id”
t.datetime “created_at”
t.datetime “updated_at”
end
create_table “statuses”, :force => true do |t|
t.string “name”
t.datetime “created_at”
t.datetime “updated_at”
end
create_table “subprofiles”, :force => true do |t|
t.integer “level_id”
t.string “title”
t.integer “company_id”
t.integer “profile_id”
t.string “description”
t.date “to_date”
t.date “from_date”
t.boolean “till_date”
t.datetime “created_at”
t.datetime “updated_at”
end
Summary:
A Contact has one Profile
A Profile has many subprofiles
A subprofile belongs to company
A subprofile belongs to level
Now, we have a search page which has following fields:
First Name
Last Name
Company Name
Title
Level id (checkboxes for all of them to make one or more selectable)
Keywords
Other than level everything is textfield hence required case insensitive
pattern matching.
Moreover, only one field should be selected otherwise it renders the
index page with error.
If keywords has some value, it supersedes other fields and look for that
in every database entity.
All we have to do is to return contacts.
Following is the code so far
def search_drive
if params[:key_words].blank?
company = params[:company]
title = params[:title]
last_name = params[:last_name]
first_name = params[:first_name]
c_level = params[:c_level]
director_level = params[:director_level]
staff = params[:staff]
vp_level = params[:vp_level]
manager_level = params[:manager_level]
other = params[:other]
else
company = title = last_name = first_name = c_level = director_level =
staff = vp_level = manager_level = other = params[:key_words]
end
search_main(company,title,last_name,first_name,c_level,director_level,staff,vp_level,manager_level,other)
end
def search_main(options = {})
contacts = []
contacts = Contacts.find(:all,:include…)
#returns contacts here i need params for find with include keyword
end
Note: My client’s server has installed MySql 4.0.x which they doesn’t
seem to upgrade in near future. So, i initially made a bulky conditional
function with “IN” sql clause. And when one of the nested queries
returns null it returns MySql error for () as a subquery results. v
4.0.x returns (empty) rather than (’’) upon empty set, so I had to
manage it manually as well (with a separate module putting commas and
braces, or (’’) upon empty set accordingly!!!)
Please help me with a neat function.
Regards,
Adeel.