Filtering Sphinx search results by date range

Question

I have Widget.title, Widget.publish_ at, and Widget.unpublish_ at. It's a rails app with thinking_sphinx running, indexing once a night. I want to find all Widgets that have 'foo' in the title, and are published (publish _at < Time.now, unpublish _at > Time.now).

To get pagination to work properly, I really want to do this in a sphinx query. I have 'has :publish_at, :unpublish_at' to get the attributes, but what's the syntax for 'Widget.search("foo @publish_ at > #{Time.now}",:match _mode=>:extended'? Is this even possible?

Answer 1

Yep, easily possible, just make sure you're covering the times in your indexes:

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

To pull it based purely off the dates, a small amount of trickery is required due to sphinx requiring a bounded range (x..y as opposed to x>=y). The use of min/max value is very inelegant, but I'm not aware of a good way around it at the moment.

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 haven't used sphinx with rails yet. But this is possible by the Sphinx API. What you need to do is to set a datetime attribute at your sphinx.conf. And don't forget to use UNIX_TIMESTAMP(publish_at), UNIX_TIMESTAMP(unpublish_at) at your index select.