I have a column in the join(middle) table. I want to search on that column that is position
here is my index file
ThinkingSphinx::Index.define :restaurant, :with => :active_record, :delta => ThinkingSphinx::Deltas::DelayedDelta do
indexes schools.school_name, :sortable => true, :as => :school_name
indexes schools.branch_name, :sortable => true, :as => :branch_name
indexes contact_info.restaurant_name, :sortable => true, :as => :restaurant_name
indexes delivery_info.delivery_charges, :as => :delivery_charges
indexes restaurant_schools.position, :as => :restaurant_position, :sortable => true
has restaurant_info.is_pick_up, :as => :pick_up, :facet => true
has delivery_info.is_delivery, :as => :delivery, :facet => true
has schools.id, :as => :school_id, :facet => true
has restaurant_categories.id, :as => :restaurant_category_ids, :facet => true
has restaurant_info.min_order, :as => :min_order, :type => :float
has avg_rating, :as => :rating, :type => :integer
has ranking, :as => :ranking, :type => :integer
has delivery_info.delivery_estimated_time, :as => :delivery_eta, :type => :integer
set_property :min_infix_len => 1
end
When i do query like:
@restaurants = Restaurant.search :with => {:school_id => school_ids }
@restaurants = @restaurants.search :order => :restaurant_position
Its not giving me records as it should be.
Please advice what should i fix the get the accurate results.
A few things to note here.
Firstly: your restaurant_position field is a collection of many values (if restaurant_schools is a has_many or has_and_belongs_to_many association, which the plural name suggests). This means it actually ends up being a string of values concatenated together (with space characters as the separator). e.g. "1 4 7 2"
Secondly: even if you changed it to an attribute, it would be a multi-value attribute. Sorting against a multi-value attribute doesn't make sense (should the average of all values be used? The smallest? The largest? The sum?).
And finally: Perhaps you're hoping the filter you're providing (on school_id) to link into the sorting logic? Sphinx is not a relational database, nor does it have any concept of hashes/dictionaries, so there's no connection between each restaurant_position value and the corresponding school they may be associated with in your database.
If you do want that connection, then it's best to have a Sphinx index on the model which has one school and one position (I'm guessing that's RestaurantSchool). From there, you can pull in relevant fields and attributes via associations, but you can also search/sort with something like this:
RestaurantSchool.search :with => {:school_id => school_ids}, :order => :position