Rails ORM Complex query help required

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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs