Filter Sphinx search results by date

Question

I have Widget.title, Widget.publish_ at and Widget.unpublish_ at. This application for rails with motion_sphinx works, indexing once a day. I want to find all Widgets that have "foo" in the title and publish (publish _at <Time.now, unpublish _at> Time.now).

To do the correct pagination, I really want to do this in an sphinx request. I have 'has: publish_at ,: unpublish_at' for getting attributes, but what is the syntax for 'Widget.search ("foo @publish_ at> # {Time.now}",: match _mode =>: extended'? Is it possible this?

Answer 1

Yes, easy, just make sure you close the time in your indices:

class Widget < ActiveRecord::Base define_index do indexes title has publish_at has unpublish_at ... end 

To pull it out based solely on dates, a small amount of cheating is required due to the sphinx, requiring a limited range (x..y unlike x> = y). Using the minimum / maximum value is very inelegant, but at the moment I do not know about it well.

 min_time = Time.now.advance(:years => -10) max_time = Time.now.advance(:years => 10) title = "foo" Widget.search title, :with => {:publish_at => min_time..Time.now, :unpublish_at => Time.now..max_time} 

Answer 2

I have not used the sphinx with rails yet. But this is possible using the Sphinx API. You need to set the datetime attribute to your sphinx.conf. And don't forget to use UNIX_TIMESTAMP (publish_at), UNIX_TIMESTAMP (unpublish_at) when choosing an index.