Thinking Sphinx with a date range

Question

I am implementing a full text search API for my rails apps, and so far have been having great success with Thinking Sphinx.

I now want to implement a date range search, and keep getting the "bad value for range" error.

Here is a snippet of the controller code, and i'm a bit stuck on what to do next.

    @search_options = { :page => params[:page], :per_page => params[:per_page]||50 }

    unless params[:since].blank?
        # make sure date is in specified format - YYYY-MM-DD
        d = nil
        begin
            d = DateTime.strptime(params[:since], '%Y-%m-%d')
        rescue
            raise ArgumentError, "Value for since parameter is not a valid date - please use format YYYY-MM-DD"
        end
        @search_options.merge!(:with => {:post_date => d..Time.now.utc})
    end
    logger.info @search_options
    @posts = Post.search(params[:q], @search_options)

When I have a look at the log, I am seeing this bit which seems to imply the date hasn't been converted into the same time format as the Time.now.utc.

withpost_date2010-05-25T00:00:00+00:00..Tue Jun 01 17:45:13 UTC 2010

Any ideas? Basically I am trying to have the API request pass in a "since" date to see all posts after a certain date. I am specifying that the date should be in the YYYY-MM-DD format.

Thanks for your help. Chris

EDIT: I just changed the date parameters merge statement to this

@search_options.merge!(:with => {:post_date => d.to_date..DateTime.now})

and now I get this error

undefined method `to_i' for Tue, 25 May 2010:Date

So obviously there is something still not setup right...

Hujsak

Answer 1

I finally solved this, but it takes a slightly different approach but it works fine.

I was trying to put the date-range search inside a sphinx_scope (in the model) or as a :condition or :with (in the controller). This did not work, so instead I had to implement it inside the define_index in the model.

So what I did was put a check in the define_index to see if a record fell within a date range, the date range being defined by some SQL code, as shown below. In this case, I wanted to see if "start_date" fell within a date between now and 30 days ago, and an "end_date" fell within today and 30 days from now.

If the dates fell within the ranges, the code below causes the :live to be 0 or 1, depending on whether it falls outside or inside the date ranges (respectively):

define index do
    # fields:
    ...
    # attributes:
    has "CASE WHEN start_date > DATE_ADD(NOW(), INTERVAL -30 DAY) AND end_date < DATE_ADD(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END", :type => :integer, :as => :live
    ...
    # delta:
    ...
end

Then in your controller, all you have to do is check if :live => 1 to obtain all records that have start_dates and end_dates within the date ranges.

I used a sphinx_scope like this:

sphinx_scope(:live) {
    { :with => { :live => 1 } }
}

and then in my controller:

@models = Model.live.search(...)

To make sure it works well, you of course need to implement frequent reindexing to make sure the index is up to date, i.e. the correct records are :live => 1 or 0!

Anyway, this is probably a bit late for you now, but I implemented it and it works like a charm!!!

Nares

Answer 2

lets say d = "2010-12-10"

:post_date => (d.to_time.to_i..Time.now.to_i) would have gotten you there. I just did this in my project and it works great

Sempach

Answer 3

Wouldn't it work if you replaced

d = DateTime.strptime(params[:since], '%Y-%m-%d')

by

Time.parse(params[:since]).strftime("%Y-%m-%d")

(It seems the first one doesn't return a date in the expected format)

Gooey