Thinking Sphinx associated searches problem

[Jason Gilmore]

Sorry the title isn't very descriptive, can't find a good way to sum up my problem.

Each location has an associated hours of operation record that I need to search on. The setup is as such:

Location:

class Location < ActiveRecord::Base

  has_many :hours

  ...

end

Hour:

class Hour < ActiveRecord::Base

attr_accessible :bar_id, :day, :start_time, :end_time

belongs_to :location

validates :location_id, :presence => true  

validates :day, :presence => true

# Thinking Sphinx #############################################

define_index do

join locations

indexes  locations .name, :as => :location_name

indexes  locations .description, :as => :location_description

has start_time, :as => :open_time

has end_time, :as => :close_time

has :location_id

has :day

has "locations.latitude",  :as => :lat,  :type => :float

has "locations.longitude", :as => :lng, :type => :float

end

sphinx_scope(:is_open) { |date,start_time=14,end_time=61|

day_num = Date.strptime(date, "%Y-%m-%d").wday

with_time_range = "*, IF(open_time < #{end_time} AND close_time >= #{start_time}, 1, 0) AS in_range"

{ :sphinx_select => with_time_range, :with => { :day => day_num, 'in_range' => 1 }, :group_by => 'bar_id', :group_function => :attr, :group_clause => "@geodist ASC" }

}

###############################################################

end

This setup works pretty good when all the hours are the same, but if even one of the hours are outside of the range I am searching, that location isn't returned. Example:

Location hours: Sun-Tue 11am - 4pm, Mon - Sat 11am - 5pm

If I search for 4pm - 7pm, it returns that location, but if I search for 5pm - 7pm, it misses that location.

I had previously searched through location model's association to hours, but that didn't give me any of the correct results, hence I arrive at this. So close,yet so far away.

Any help would be appreciated.

Jason Gilmore

[Jason Gilmore]

A bit more info on the problem of using the location based search:

It would return the location if it was open OR if it had hours in the range specified. i.e. It would return a location that was only open Mon-Fri 10am-6pm if I searched for Sat 2pm-4pm.

I think this is due to how Sphinx indexes its documents, but I don't know enough about it to be sure.

[Jason Gilmore]

Now I feel silly...

I got it to work. Turns out the problem was that I was using Time.now.wday, which returns Sunday as 0, when my app was assuming Monday is 0. I put in a little converter for that and now it works as intended. Yay!

Jason Gilmore

Developer - 42