How to restrict text search to a certain subset of the database ?

Question

I have a large central database of around 1 million heavy records. In my app, for every user I would have a subset of rows from central table, which would be very small (probably 100 records each).When a particular user has logged in , I would want to search on this data set only. Example:

Say I have a central database of all cars in the world. I have a user profile for General Motors(GM) , Ferrari etc. When GM is logged in I just want to search(a full text search and not fire a sql query) for those cars which are manufactured by GM. Also GM may launch/withdraw a model in which case central db would be updated & so would be rowset associated with GM. In case of acquisitions, db of certain profiles may change without launch/removal of new car. So central db wont change then , but rowsets may.

Whats the best way to implement such a design ? These smaller row sets would need to be dynamic depending on user activities.

We are on Rails 2.3.5 and use thinking_sphinx as the connector and Sphinx/MySQL for search and relational associations.

Answer 1

how about using has_many :through

class Manufacturer
class Car
class ManufacturerCarRelation

Manufacturer
 has_many :manufacturer_car_relations
 has_many :cars through => manufacturer_car_relations

ManufacturerCarRelation
 belongs_to :manufacturer
 belongs_to :car

Answer 2

Maybe you want to define your index with something like this:

class Car
  define_index do
    indexes description
    has 'cars.manufacturer_id', :as => :manufacturer_id, :type => :integer
  end
end

and then use field conditions, like:

Car.search "red", :conditions => {:manufacturer_id => gm.id}

or attribute filters:

Car.search "red", :with => {:manufacturer_id => gm.id}