Search Issue by using sphinx in ROR project

Hello everyone:

I have a ROR Project with db structure like this:

class Business
  has_many :business_categories,
    :include => :business,
    :conditions => ["businesses.version = business_categories.version"]
  has_many :categories, :through => :business_categories, :source => :category
end

class Category
  has_many :business_categories,
    :include => :business,
    :conditions => ["businesses.version = business_categories.version"]
  has_many :businesses, :through => :business_categories, :source => :business
end

class BusinessCategory
  belongs_to :category
  belongs_to :business
end

a category has many businesses, a business belongs to several categories. And each changes to a business become a new version.

And there is a thinking_sphinx configuration in business model

class Business
  indexes :name, :sortable => true
  has business_categories(:category_id), :as => :category_ids
  #indexes categories(:name), :as => :category_name
end

Because of the "version" column, sphinx looks like does not recognize the "categories(name)", here is the error message I got:

sql_range_query: Unknown column 'business_categories.version' in 'on clause'

After tried severl solutions but failed, I have to round the categories(:name), search businesses only by the category ids

Now I met a new situation:

The customer want to search businesses by type category name. When the user type "Restaurants" as keyword, it should return all the businesses under the category: restaurants. Because there is no business which has keyword "restaurants" in the name, so in this case I removed the "restaurants" from the keywords and write some code to find out the category id and return all the businesses under the category. This case is pretty ok.

Then, There is a category named "Coffee", in this category there are severl businesses which has the keyword "coffee" in the name. And, there are several businesses not in the category "Coffee" but "Food" which has "coffee" in the name. If I use the solution of the first case, return all the businesses from coffee category, the businesses in Food category will not appear in the search result, it is not correct. If using the "coffee" just as the keyword, then the businesses which does not have keyword "coffee" in the name will not shown in the result, and this will conflict with the first case, the first case will return nothing in this solution.

So how can I use the keyword "Coffee" to get all the businesses under the "coffee" category and those businesses which not in "coffee" category but has "coffee" in the name?

Any ideas?

ansaurus

tags:

views:

answers:

Search Issue by using sphinx in ROR project

related questions