Searching database in Rails with multiple optional parameters

Question

I have a Rails app with following schema:

User (id, username, user_type, master_id) Item ( id,itemname,is_master,master_id) Repairs (id, in_date, out_date, user_id, item_id)

The app is small so I have tried keeping db as simple as possible. A user can be Admin, Dealer or Manager. A manager can have many dealers under him(Both stored in same user table).

An Item can be a master item like a computer(with many sub items like keyboard,mouse) or it can be just a small sub item. A master item can have many sub items under it.

A repair is Repair history for item. A repair has an item and user associated.

I have a search for with following parameters ( ALl optional)

Manager Name
Dealer Name
Master Item
Sub Item
In date
Out date

If no Dealer Name is specified I want results to have all Dealers in the manager specified, if no Sub Item is specified I want all items under Master Item to be in the results.

How should the search controller be defined so as to keep code simple and maintainable? I dont really want to do an 'if' for each condition.

If I understand the intent well (and it's quite possible I don't, as the question is a little bit vague), you want to be able to search items by equality on any of the specified fields, some of which are columns on other tables? — heartpunk
– heartpunk, Commented Dec 29, 2012 at 10:32

heartpunk · Accepted Answer · 2012-12-29 10:53:06Z

1

Item.joins :repairs => :user

Will do the join from items to repairs to users for you. After that it's just a matter of filtering by equality on the various fields, if they are provided. Fortunately, you can do that very idiomatically in rails by constructing your search form such that the field names match up with what they should be in the hash for a .where.

You'll need to filter out all the parameters that are blank (empty or nil), depending on how your view is structured. params.reject {|k,v| v.blank?} should do that for you.

Putting it all together, then

Item.joins(:repairs => :user).where(params.reject {|k,v| v.blank?})

or something close to that might do the trick, if you have set up the association helpers (which you absolutely should).

edited Dec 29, 2012 at 10:53

answered Dec 29, 2012 at 10:35

heartpunk

2,2952 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nitish Upreti Over a year ago

The problem is that my db table is too simple and the associations has_many between manager and dealer and master item and subitem are not defined. I have to manually find results based on the ids of users and items. ( For instance if just the manager is specified, I first need to see all dealers under the manager based on the id and then check the Repairs table, same goes for MasterItem and SubItems in similar conidtions)

heartpunk Over a year ago

Deleted a previous comment... It seems you could do a nested join instead, if you use the associations.

heartpunk Over a year ago

Updated my response to reflect my better understanding. Let me know if it doesn't solve the problem.

Nitish Upreti Over a year ago

What would be the best way out in present scenario? I am clueless. :|

heartpunk Over a year ago

Significantly expanded my answer. Let me know if you're confused after that.

Collectives™ on Stack Overflow

Searching database in Rails with multiple optional parameters

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related