Rails: How to implement search through association

Question

Original Question

My app deals with "Machines". Machines have specifications, written as many "Specs". Each of those Specs contains a reference to "Spec_Field" (Fields such as "Weight", "Height", "Capacity", etc), and a value.

Class Machine
  has_many :specs
  has_many :spec_fields, :through => :specs

Class Spec
  belongs_to :machine
  belongs_to :spec_field

What I want to do is to have a Search function in the #show action for each Machine, where a user checks off the Specs that he wants to match ("Yes, length 50 is what I need, and capacity 500 is perfect, I don't care for width"), and clicks "Find Similar", which would then find all Machines with similar Specs.

I assume I'll need a model for Search, similar to the following screencast: http://railscasts.com/episodes/111-advanced-search-form-revised?autoplay=true

In the #show action for a Machine, I will then need to go through this Machine's Specs, and add them to the "Search" instance. So does that mean a "Spec" also needs to belong to "Search"?

I just can't wrap my mind around how to organize everything in my case. Any help is appreciated!

Database Schema

Machines:

t.string   "title"
t.text     "description"
t.datetime "created_at",                      :null => false
t.datetime "updated_at",                      :null => false
t.string   "status"
t.integer  "category_id"
t.string   "manufacturer"
t.string   "model"
t.string   "serial"
t.string   "year"
t.string   "location"

Specs:

t.integer  "machine_id"
t.integer  "field_id"
t.text     "value"
t.float    "base_value"
t.integer  "unit_id"
t.datetime "created_at", :null => false
t.datetime "updated_at", :null => false

Spec Fields:

t.string  "label"
t.integer "base_units"

Solution That Seems to Work

I added a "With Spec" scope that uses SQL "IN" with "SELECT"

 def self.with_spec(field_id,value)
    min_value = value.to_f * 0.8
    max_value = value.to_f * 1.2
    where("machines.id IN (SELECT machine_id FROM specs WHERE field_id = ? AND value > ? AND value < ?)",field_id,min_value,max_value)
  end

If it does not overkill, you can try the full-text search, for example sunspot.github.com — Mik
– Mik, Commented May 2, 2012 at 19:45

Unixmonkey · Accepted Answer · 2012-05-02 19:46:47Z

I would build out what I call "filter scopes"; they will reduce the result set if you pass in an hash with approprately named keys. If what is passed in is nil or blank, it ignores that condition.

Then I define a class search method that combines all the filter searches like so:

Class Spec
  belongs_to :machine
  belongs_to :spec_field

  # filter scopes 
  scope :with_machine,  lambda{|*term| where('machine_id = ?',term) if term.present? }
  scope :with_length,   lambda{|*term| where('length = ?',term)     if term.present? }
  scope :with_capacity, lambda{|*term| where('capacity = ?',term)   if term.present? }
  scope :with_width,    lambda{|*term| where('width = ?',term)      if term.present? }

  def self.filter_search(options)
    with_machine(  options[:machine]  ).
    with_length(   options[:length]   ).
    with_capacity( options[:capacity] ).
    with_width(    options[:width]    )
  end

end

Now all you need is to build a form that will have appropriately named inputs:

<%= text_field_tag 'search[machine]', @machine.id %>
<%= text_field_tag 'search[length]' %>
<%= text_field_tag 'search[capacity]' %>
<%= text_field_tag 'search[width]' %>
<%= submit_tag %>

And call it on the controller:

@specs = Spec.filter_search(params[:search]).all

An alternative would be to setup a full-text search engine, and build your search method in much the same way.

This looks interesting, but "Spec" model does not have fields such as "length", "capacity", etc. Instead, it points to another table ("Spec_Fields") via "field_id". This is to allow unlimited number of spec fields, and to allow users to create their own spec fields.
@ef-n It might be helpful if you posted the relevant tables from your schema.rb

caulfield · Accepted Answer · 2012-05-02 21:19:32Z

0

I think gem meta_search can help you and simplify your code.

answered May 2, 2012 at 21:19

caulfield

1,3731 gold badge11 silver badges22 bronze badges

Collectives™ on Stack Overflow

Rails: How to implement search through association

Original Question

Database Schema

Solution That Seems to Work

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

Original Question

Database Schema

Solution That Seems to Work

2 Answers 2

2 Comments

Comments

Related