# Rails ORM Complex query help required

**URL:** <https://www.ruby-forum.com/t/rails-orm-complex-query-help-required/175232>\
**Category:** Rails\
**Created:** [September 10, 2009, 1:35am UTC](https://www.ruby-forum.com/t/rails-orm-complex-query-help-required/175232 "2009-09-10T01:35:28Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![adeel\_bm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/47e85d/32.png) [@adeel\_bm](https://www.ruby-forum.com/u/adeel_bm)\
**Post date:** [September 10, 2009, 1:35am UTC](https://www.ruby-forum.com/t/rails-orm-complex-query-help-required/175232/1 "2009-09-10T01:35:28Z")

</div>

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.